<?php
Globals::requireClass('Controller');
Globals::requireTable('Orderdetail');
Globals::requireTable('Order');
Globals::requireTable('Box');
Globals::requireTable('Yard');
Globals::requireTable('Ordermoney');
Globals::requireTable('Custom');
Globals::requireTable('Custom_contact');
Globals::requireTable('Servicemode');
Globals::requireTable('Carteam');
Globals::requireTable('Custom_site');
Globals::requireTable('Car');
Globals::requireTable('Driver');
Globals::requireTable('Carteam');

class FinanceController extends Controller {

    protected $CustomTable;
    protected $OrderdetailTable;
    protected $OrderTable;
    protected $BoxTable;
    protected $YardTable;
    protected $OrdermoneyTable;
    protected $Custom_contactTable;
    protected $ServicemodeTable;
    protected $CarteamTable;
    protected $Custom_siteTable;
    protected $CarTable;
    protected $DriverTable;
    public static $defaultConfig = array(
        'viewEnabled' => true,
        'layoutEnabled' => true,
        'title' => null
    );

    public function __construct($config = null) {
        parent::__construct($config);
        $this->OrderdetailTable = new OrderdetailTable($config);
        $this->OrderTable = new OrderTable($config);
        $this->BoxTable = new BoxTable($config);
        $this->YardTable = new YardTable($config);
        $this->OrdermoneyTable = new OrdermoneyTable($config);
        $this->CustomTable = new CustomTable($config);
        $this->Custom_contactTable = new Custom_contactTable($config);
        $this->ServicemodeTable = new ServicemodeTable($config);
        $this->CarteamTable = new CarteamTable($config);
        $this->Custom_siteTable = new Custom_siteTable($config);
        $this->CarTable = new CarTable($config);
        $this->DriverTable = new DriverTable($config);
        $this->CarteamTable = new CarteamTable($config);
        if (!in_array(14, $_SESSION['userinfo']['acl'])) {
            $this->redirect("./?m=index&a=main");
            exit;
        }
    }

    protected function out() {
        $this->layout->nav = 'index';
        parent::out();
    }

    public function accountreceivablepAction() {
        $this->config['layoutEnabled'] = false;
        $custom_id = $this->getIntParam('custom_id');
        $contact = $this->getIntParam('contact');
        $status = $this->getIntParam('status');
        $servicemode_id = $this->getIntParam('servicemode');
        $start = $this->getParam('start');
        $end = $this->getParam('end');
        //  echo $start;
        if (!empty($custom_id)) {
            $where[] = "`custom_id` = $custom_id";
            $this->view->custom_id = $custom_id;
        } else {
            $this->view->custom_id = 0;
        }
        if (!empty($contact)) {
            $where[] = "`custom_contact_id` = $contact";
            $this->view->contact_id = $contact;
        } else {
            $this->view->contact_id = 0;
        }
        if (!empty($servicemode_id)) {
            $where[] = "`service_mode_id` = $servicemode_id";
            $this->view->servicemode_id = $servicemode_id;
        }

        if (!empty($status)) {
            $where[] = "`status` = $status";
            $this->view->status = $status;
        } else {
            $where[] = "`status` > 0";
        }
        $zwhere = "`status` = 3";
        if (!empty($start)) {
            $zwhere[] = "`dobox_date` >= '{$start}'";
            $this->view->start = $start;
        }
        if (!empty($end)) {
            $zwhere[] = "`dobox_date` <= '{$end}'";
            $this->view->end = $end;
        }
        //var_dump($zwhere);
        $zdata = $this->OrderdetailTable->listAll($zwhere);
        foreach ((array) @$zdata as $k => $v) {
            $z_ids[] = $v['orderid'];
            $moneyids[] = $v['id'];
        }
        $moneyids = @array_unique($moneyids);
        $moneyar = @implode(",", $moneyids);
        $z_ids = @array_unique($z_ids);
        $z_ar = @implode(",", $z_ids);
        if (!empty($z_ar)) {
            $where[] = "`orderid` in ({$z_ar})";
        } else {
            $where[] = "`orderid` = '就是应该找不到'";
        }
        $count = $this->OrderTable->listCount($where);
        $pageId = $this->getIntParam('page');
        $pageSize = 5;
        $this->view->paging = $this->getPaging($count, $pageSize, $pageId);
        $data = $this->OrderTable->listPage($where, "id desc", $pageSize, $pageId);
        $this->view->data = isset($data) ? $data : array();
        //获取orderid
        $orderid_ids = array();
        foreach ((array) @$data as $k => $v) {
            $orderid_ids[] = $v['orderid'];
        }
        $orderid_ids = array_unique($orderid_ids);
        $orderid_ar = implode(",", $orderid_ids);
        if (!empty($orderid_ar)) {
            $orderiddata = $this->OrderdetailTable->listAll("`orderid` in ({$orderid_ar}) AND `status` = 3");
            //$money = $this->OrdermoneyTable->listAll("`orderid` in ({$orderid_ar}) AND `type` = 1");
        }
        if (!empty($moneyar)) {
            $money = $this->OrdermoneyTable->listAll("`detail_id` in ({$moneyar}) AND `type` = 1");
        }
        foreach ((array) @$money as $k => $v) {
            $moneydata[$v['orderid']][] = $v;
            $price[$v['orderid']][$v['detail_id']] = $v;
        }
        //箱型
        $this->view->price = isset($price) ? $price : array();
        $x1 = array();
        //统计箱型，数量
        $number = array();
        $number_data = array();
        foreach ((array) @$orderiddata as $k => $v) {
            $x1[] = (int) $v['box_id'];
            $number_data[$v['orderid']][] = (int) $v['box_id'];
            $boxnumber[$v['orderid']][] = $v['box_code'];
        }
//        $this->view->money = isset($moneydata) ? $money : array();
//        $this->view->boxnumber = isset($boxnumber) ? $boxnumber : array();
        foreach ((array) @$number_data as $k => $v) {
            $number[$k] = @array_count_values($v); //数量统计
        }
        // var_dump($number);exit;
        $x1 = @array_unique($x1);
        $x1_arr = @implode(",", $x1);
        if (!empty($x1_arr)) {
            $boxdata = $this->BoxTable->listAll("`id` in ({$x1_arr})");
        }
        $box = array();
        foreach ((array) @$boxdata as $k => $v) {
            $box[$v['id']] = $v['code']; //box 的编号  
        }
//        $this->view->box = isset($box) ? $box : array();
//        $this->view->number = isset($number) ? $number : array();
        /*         * ******堆场信息******** */
        foreach ((array) @$data as $k => $v) {
            $x2[] = $v['send_yard_id'];
            $x2[] = $v['back_yard_id'];
        }
        $x2 = @array_unique($x2);
        $x2_ar = @implode(",", $x2);
        if (!empty($x2_ar)) {
            $x2data = $this->YardTable->listAll("`id` in ({$x2_ar})");
        }
        foreach ((array) @$x2data as $k => $v) {
            $x2_data[$v['id']] = $v['address']; //拿地址
        }
        // var_dump($x2_data);exit;
        $this->view->x2 = isset($x2_data) ? $x2_data : array();

        $contact = $this->Custom_contactTable->listAll("`status` = 1");
        foreach ($data as $k => $v) {
            $object[$k]['id'] = $v['id'];
            $object[$k]['createtime'] = $v['createtime'];
            $object[$k]['ship_date'] = $v['ship_date'];
            $object[$k]['custom_code'] = $v['custom_code'];
            $object[$k]['lading_code'] = $v['lading_code'];
            $object[$k]['cmhc'] = $v['ship_name'] . '/' . $v['ship_line'];
            $a = '';
            $d = '';
            foreach ((array) @$number[$v['orderid']] as $sk => $sv) {
                $a .= $d . @$box[$sk] . '*' . $sv;
                $d = ',';
            }
            $c = '';
            foreach ((array) @$boxnumber[$v['orderid']] as $kk => $kv) {
                $c .= $kv . '<br/>';
            }

            $p1 = 0;
            foreach ((array) @$price[$v['orderid']] as $tk => $tv) {
//                                              $p1 = 0;
                $p1 = $p1 + $tv['money_1'];
            }
            $p5 = 0;
            foreach ((array) @$price[$v['orderid']] as $tk => $tv) {
//                                              $p1 = 0;
                $p5 = $p5 + $tv['money_5'];
            }

            $p6 = 0;
            foreach ((array) @$price[$v['orderid']] as $tk => $tv) {
                $p6 = $p6 + $tv['money_6'];
            }
            $p7 = 0;
            foreach ((array) @$price[$v['orderid']] as $tk => $tv) {
//                                              $p1 = 0;
                $p7 = $p7 + $tv['money_7'];
            }

            $p10 = 0;
            foreach ((array) @$price[$v['orderid']] as $tk => $tv) {
//                                              $p1 = 0;
                $p10 = $p10 + $tv['money_10'];
            }

            $p11 = 0;
            foreach ((array) @$price[$v['orderid']] as $tk => $tv) {
//                                              $p1 = 0;
                $p11 = $p11 + $tv['money_11'];
            }
            $p15 = 0;
            foreach ((array) @$price[$v['orderid']] as $tk => $tv) {
//                                              $p1 = 0;
                $p15 = $p15 + $tv['money_15'];
            }
            $total = $p1 + $p5 + $p6 + $p7 + $p10 + $p11 + $p15;
            $object[$k]['total'] = $total;
            $object[$k]['money_11'] = $p11;
            $object[$k]['money_15'] = $p15;
            $object[$k]['money_10'] = $p10;
            $object[$k]['money_7'] = $p7;
            $object[$k]['money_1'] = $p1;
            $object[$k]['money_6'] = $p6;
            $object[$k]['money_5'] = $p5;
            $object[$k]['xxxl'] = $a; //箱型箱量
            $object[$k]['xh'] = $c; //箱号
            $object[$k]['send_yard_id'] = @$x2_data[$v['send_yard_id']];
            $object[$k]['goods_city'] = @$x2_data[$v['goods_city']];
            $object[$k]['back_yard_id'] = @$x2_data[$v['back_yard_id']];
        }
        $servicemode = $this->ServicemodeTable->listAll("`status` = 1");
        $custom = $this->CustomTable->listAll("`status` = 1");
        $this->view->servicemode = isset($servicemode) ? $servicemode : array();
        $this->view->contact = isset($contact) ? $contact : array();
        $this->view->custom = isset($custom) ? $custom : array();
        $this->view->object = isset($object) ? $object : array();
        $type = $this->getIntParam("type");
        if ($type == 2) {
            error_reporting(E_ALL);
            require_once('./model/PHPExcel.php');
            date_default_timezone_set('PRC');
            $objPHPExcel = new PHPExcel();
            //设置基本属性
            $objPHPExcel->getProperties()->setCreator("lj")
                    ->setLastModifiedBy("lj")
                    ->setTitle("Office 2003 XLSX Test Document")
                    ->setSubject("Office 2003 XLSX Test Document")
                    ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
                    ->setKeywords("office 2003 openxml php")
                    ->setCategory("Test result file");
            //设置工作薄索引
            $objPHPExcel->setActiveSheetIndex(0);

            //设置工作薄名称
            $objAtvSheet = $objPHPExcel->getActiveSheet();
            $objAtvSheet->setTitle("生成报表1");

            //设置标题
            $objAtvSheet->setCellValue('A1', '应收客户对账单(票)');
            $objAtvSheet->mergeCells('A1:S1'); //合并单元格
            //$objAtvSheet->mergeCells('A2:X2');
            //设置单元格内容
            $objAtvSheet->setCellValue('A2', '序号');
            $objAtvSheet->setCellValue('B2', '日期');
            $objAtvSheet->setCellValue('C2', '船期');
            $objAtvSheet->setCellValue('D2', '客户编号');
            $objAtvSheet->setCellValue('E2', '提单号');
            $objAtvSheet->setCellValue('F2', '航名/船次');
            $objAtvSheet->setCellValue('G2', '箱型/箱类');
            $objAtvSheet->setCellValue('H2', '提柜点');
            $objAtvSheet->setCellValue('I2', '装卸点');
            $objAtvSheet->setCellValue('J2', '还柜点');
            $objAtvSheet->setCellValue('K2', '箱号');
            $objAtvSheet->setCellValue('L2', '运费');
            $objAtvSheet->setCellValue('M2', '落/还箱费');
            $objAtvSheet->setCellValue('N2', '打/改单费');
            $objAtvSheet->setCellValue('O2', '理货费');
            $objAtvSheet->setCellValue('P2', '港杂费');
            $objAtvSheet->setCellValue('Q2', '坏/污箱费');
            $objAtvSheet->setCellValue('R2', '代收费');
            $objAtvSheet->setCellValue('S2', '小计');

            $count = count($object);
            // echo $count;exit;
            $RetentionRow = 3;                //设置开始的一行
            //得到最后一行
            $endLine = $RetentionRow + $count + 1;
            //设置表头背景颜色
            $header_Background = new PHPExcel_Style();
            $header_Background->applyFromArray(
                    array(
                        'fill' => array(
                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                            'color' => array('argb' => 'FF888888')
                        ),
                        'borders' => array(
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($header_Background, "A2:S2");

            //设置内容的边框,注意：这里要先设置边框 在才能设置单元格里的字体颜色
            $content_Border = new PHPExcel_Style();
            $content_Border->applyFromArray(
                    array(
                        'borders' => array(
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($content_Border, "A3:S$endLine");
            //开始填充数据到excel
            $i = 0;
            $money_1_sum = 0;
            $money_5_sum = 0;
            $money_6_sum = 0;
            $money_7_sum = 0;
            $money_10_sum = 0;
            $money_11_sum = 0;
            $money_15_sum = 0;
            $heji_sum = 0;
            foreach ($object as $k => $v) {
                $line = $i + $RetentionRow; //开始填充的行数
                $s1 = $v['id'];
                $s2 = $v['createtime'];
                $s3 = $v['ship_date'];
                $s4 = $v['custom_code'];
                $s5 = $v['lading_code'];
                $s6 = $v['cmhc'];
                $s7 = $v['xxxl'];
                $s8 = $v['send_yard_id'];
                $s9 = $v['goods_city'];
                $s10 = $v['back_yard_id'];
                $xh = explode("<br/>", $v['xh']);
                if (count($xh) > 2) {
                    $s11 = implode(",", $xh);
                } else {
                    $s11 = $xh[0];
                }

                //$s11  = $v['xh'];
                $s12 = $v['money_1'];
                $money_1_sum += $v['money_1'];
                $s13 = $v['money_5'];
                $money_5_sum += $v['money_5'];
                $s14 = $v['money_6'];
                $money_6_sum += $v['money_6'];
                $s15 = $v['money_7'];
                $money_7_sum += $v['money_7'];
                $s16 = $v['money_10'];
                $money_10_sum += $v['money_10'];
                $s17 = $v['money_11'];
                $money_11_sum += $v['money_11'];
                $s18 = $v['money_15'];
                $money_15_sum += $v['money_15'];
                $heji = ($v['money_1'] + $v['money_5'] + $v['money_6'] + $v['money_7'] + $v['money_10'] + $v['money_11'] + $v['money_15']);
                $s19 = $heji;
                $heji_sum += $heji;

                $objAtvSheet->setCellValue("A$line", $s1);
                $objAtvSheet->setCellValue("B$line", $s2);
                $objAtvSheet->setCellValue("C$line", $s3);
                $objAtvSheet->setCellValue("D$line", $s4);
                $objAtvSheet->setCellValue("E$line", $s5);
                $objAtvSheet->setCellValue("F$line", $s6);
                $objAtvSheet->setCellValue("G$line", $s7);
                $objAtvSheet->setCellValue("H$line", $s8);
                $objAtvSheet->setCellValue("I$line", $s9);
                $objAtvSheet->setCellValue("J$line", $s10);
                $objAtvSheet->setCellValue("K$line", $s11);
                $objAtvSheet->setCellValue("L$line", $s12);
                $objAtvSheet->setCellValue("M$line", $s13);
                $objAtvSheet->setCellValue("N$line", $s14);
                $objAtvSheet->setCellValue("O$line", $s15);
                $objAtvSheet->setCellValue("P$line", $s16);
                $objAtvSheet->setCellValue("Q$line", $s17);
                $objAtvSheet->setCellValue("R$line", $s18);
                $objAtvSheet->setCellValue("S$line", $s19);
                $i++;
            }
            $tline = $line + 1;
            $xline = $tline + 1;
            $big_count = get_amount($heji_sum);
            $objAtvSheet->setCellValue("A$tline", "合计:");
            $objAtvSheet->setCellValue("A$xline", "合计(大写):");
            $objAtvSheet->setCellValue("L$tline", "$money_1_sum");
            $objAtvSheet->setCellValue("M$tline", "$money_5_sum");
            $objAtvSheet->setCellValue("N$tline", "$money_6_sum");
            $objAtvSheet->setCellValue("O$tline", "$money_7_sum");
            $objAtvSheet->setCellValue("P$tline", "$money_10_sum");
            $objAtvSheet->setCellValue("Q$tline", "$money_11_sum");
            $objAtvSheet->setCellValue("R$tline", "$money_15_sum");
            $objAtvSheet->setCellValue("S$tline", "$heji_sum");
            $objAtvSheet->setCellValue("L$xline", "$big_count");
            $objAtvSheet->mergeCells("A$tline:K$tline");
            $objAtvSheet->mergeCells("A$xline:K$xline");
            $objAtvSheet->mergeCells("L$xline:S$xline");

            //设置 字体加粗，居中
            $objAtvSheet->getStyle('A1:S1')->getFont()->setBold(true);
            $objAtvSheet->getStyle('A1:S1')->getFont()->setSize(20);
            $objAtvSheet->getStyle('A1:S1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objAtvSheet->getStyle('A1:S1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objAtvSheet->getStyle('A1:S1')->getFont()->setName('新宋体');
            $objAtvSheet->getStyle('A1:S1')->getFont()->getColor()->setRGB('E85656');
            $objAtvSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置内容 居中
            $objAtvSheet->getStyle("A3:S$endLine")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格宽度
//    $objAtvSheet->getColumnDimension('A')->setWidth(20);
//    $objAtvSheet->getColumnDimension('B')->setWidth(20);
//    $objAtvSheet->getColumnDimension('C')->setWidth(20);
//    $objAtvSheet->getColumnDimension('D')->setWidth(20);
//    $objAtvSheet->getColumnDimension('E')->setWidth(20);
//    $objAtvSheet->getColumnDimension('F')->setWidth(20);
//    $objAtvSheet->getColumnDimension('G')->setWidth(20);
//    $objAtvSheet->getColumnDimension('H')->setWidth(20);
//    $objAtvSheet->getColumnDimension('I')->setWidth(20);
//    $objAtvSheet->getColumnDimension('J')->setWidth(20);
//    $objAtvSheet->getColumnDimension('K')->setWidth(20);
//    $objAtvSheet->getColumnDimension('L')->setWidth(20);
//    $objAtvSheet->getColumnDimension('M')->setWidth(20);
//    $objAtvSheet->getColumnDimension('N')->setWidth(20);
//    $objAtvSheet->getColumnDimension('O')->setWidth(20);
//    $objAtvSheet->getColumnDimension('P')->setWidth(20);
//    $objAtvSheet->getColumnDimension('Q')->setWidth(20);
//    $objAtvSheet->getColumnDimension('R')->setWidth(20);
//    $objAtvSheet->getColumnDimension('S')->setWidth(20);
//    $objAtvSheet->getColumnDimension('T')->setWidth(20);
//    $objAtvSheet->getColumnDimension('U')->setWidth(20);
//    $objAtvSheet->getColumnDimension('V')->setWidth(20);
//    $objAtvSheet->getColumnDimension('W')->setWidth(20);
//    $objAtvSheet->getColumnDimension('X')->setWidth(20);


            $dateString = date('YmdHis') . "应收客户对账单(票).xls";
            $outputFileName = $dateString;
            header('Content-Type: application/vnd.ms-excel');
            header('Content-type: text/csv');
            header('Content-Disposition: attachment;filename="' . $outputFileName . '"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
        }
    }

    public function accountreceivablexAction() {
        $this->config['layoutEnabled'] = false;
        $custom_id = $this->getIntParam('custom_id');
        $contact = $this->getIntParam('contact');
        $status = $this->getIntParam('status');
        $servicemode_id = $this->getIntParam('servicemode');
        $start = $this->getParam('start');
        $end = $this->getParam('end');
        // echo $status;
        /*         * *****主订单过滤******** */
        $zwhere [] = "status = 1";
        if (!empty($custom_id)) {
            $zwhere[] = "`custom_id` = $custom_id";
            $this->view->custom_id = $custom_id;
        } else {
            $this->view->custom_id = 0;
        }
        if (!empty($contact)) {
            $zwhere[] = "`custom_contact_id` = $contact";
            $this->view->contact_id = $contact;
        } else {
            $this->view->contact_id = 0;
        }
        if (!empty($servicemode_id)) {
            $zwhere[] = "`service_mode_id` = $servicemode_id";
            $this->view->servicemode_id = $servicemode_id;
        }

        $condition = $this->OrderTable->listAll($zwhere);
        $condition_ids = array();
        foreach ((array) $condition as $k => $v) {
            $condition_ids[] = $v['orderid'];
        }
        $condition_ar = implode(",", $condition_ids);
//        if (!empty($status)) {
//            $where[] = "`status` = $status";
//            $this->view->status = $status;
//        } else {
//            $where[] = "`status` > 0";
//        }
        $where[] = "`status` = 3";
        if (!empty($start)) {
            $where[] = "`dobox_date` >= '{$start}'";
            $this->view->start = $start;
        }
        if (!empty($end)) {
            $where[] = "`dobox_date` <= '{$end}'";
            $this->view->end = $end;
        }
        if (!empty($condition_ar)) {
            $where[] = "`orderid` in ({$condition_ar})";
        }

        $count = $this->OrderdetailTable->listCount($where);
        $pageId = $this->getIntParam('page');
        $pageSize = 5;
        $this->view->paging = $this->getPaging($count, $pageSize, $pageId);
        /** 根据orderid找客户编号* */
        $data = $this->OrderdetailTable->listPage($where, "id desc", $pageSize, $pageId);
        $ids = array();
        $money_ids = array(); //费用信息
        $x1 = array(); //箱型
        foreach ((array) $data as $k => $v) {
            $ids[] = $v['orderid'];
            $money_ids[] = $v['id'];
            $x1[] = (int) $v['box_id'];
        }
        $ids = array_unique($ids);
        $money_ids = array_unique($money_ids);
        $money_ar = implode(",", $money_ids);
        //echo $money_ar;exit;
        $x1 = array_unique($x1);
        $ids_ar = implode(',', $ids);
        $x1_ar = implode(',', $x1);

        if (!empty($ids_ar)) {
            $orderdata = $this->OrderTable->listAll("`orderid` in ({$ids_ar})");
        }

        if (!empty($money_ar)) {
            $moneydata = $this->OrdermoneyTable->listAll("`detail_id` in ({$money_ar}) AND `type` = 1");
        }

        foreach ((array) @$moneydata as $k => $v) {
            $money_data[$v['detail_id']] = $v;
        }

        if (!empty($x1_ar)) {
            $x1data = @$this->BoxTable->listAll("`id` in ({$x1_ar})");
        }

        $order_data = array();
        $x1_data = array();
        $money = array();
        $x2 = array(); //提柜点,还柜点
        $x3 = array(); //装卸点

        foreach ((array) @$orderdata as $k => $v) {
            $order_data[$v['orderid']] = $v;
            $x2[] = $v['send_yard_id'];
            $x2[] = $v['back_yard_id'];
        }
        $x2 = array_unique($x2);
        $x2_ar = implode(",", $x2);
        if (!empty($x2_ar)) {
            $x2data = $this->YardTable->listAll("`id` in ({$x2_ar})");
        }
        foreach ((array) @$x2data as $k => $v) {
            $x2_data[$v['id']] = $v['address']; //拿地址
        }
        foreach ((array) @$x1data as $k => $v) {
            $x1_data[$v['id']] = $v['describe']; //箱型拿的是描述
        }

//        /*         * *客户简称** */
//        /*         * 客户联系人* */
        foreach ($data as $k => $v) {
            $object[$k]['id'] = $v['id'];
            $object[$k]['createtime'] = $v['createtime']; //创建时间
            $object[$k]['custom_code'] = $order_data[$v['orderid']]['custom_code']; //客户编号
            $object[$k]['cmhc'] = $order_data[$v['orderid']]['ship_name'] . '/' . $order_data[$v['orderid']]['ship_line']; //船名航次
            $object[$k]['lading_code'] = $order_data[$v['orderid']]['lading_code']; //提单号
            $object[$k]['box_id'] = @$x1_data[$v['box_id']]; //箱型
            $object[$k]['send_yard_id'] = $x2_data[$order_data[$v['orderid']]['send_yard_id']]; //提柜点
            $object[$k]['goods_city'] = $x2_data[$order_data[$v['orderid']]['goods_city']]; //装卸点
            $object[$k]['back_yard_id'] = $x2_data[$order_data[$v['orderid']]['back_yard_id']]; //还柜点
            $object[$k]['money_1'] = $money_data[$v['id']]['money_1'];
            $object[$k]['money_5'] = $money_data[$v['id']]['money_5'];
            $object[$k]['money_6'] = $money_data[$v['id']]['money_6'];
            $object[$k]['money_7'] = $money_data[$v['id']]['money_7'];
            $object[$k]['money_10'] = $money_data[$v['id']]['money_10'];
            $object[$k]['money_11'] = $money_data[$v['id']]['money_11'];
            $object[$k]['count'] = $money_data[$v['id']]['money_1'] + $money_data[$v['id']]['money_5'] + $money_data[$v['id']]['money_6'] + $money_data[$v['id']]['money_7'] + $money_data[$v['id']]['money_10'] + $money_data[$v['id']]['money_11'];
            $object[$k]['money_remark'] = $money_data[$v['id']]['money_remark'];
        }
        // var_dump($object);
        $contact = $this->Custom_contactTable->listAll("`status` = 1");
        // var_dump($contact);exit;
        /*         * 业务方式* */
        $servicemode = $this->ServicemodeTable->listAll("`status` = 1");
        $custom = $this->CustomTable->listAll("`status` = 1");
        $this->view->servicemode = isset($servicemode) ? $servicemode : array();
        $this->view->contact = isset($contact) ? $contact : array();
        $this->view->custom = isset($custom) ? $custom : array();
        $this->view->object = isset($object) ? $object : array();
        $type = $this->getIntParam('type');
        if ($type == 2) {
            error_reporting(E_ALL);
            require_once('./model/PHPExcel.php');
            date_default_timezone_set('PRC');

            $objPHPExcel = new PHPExcel();

            //设置基本属性
            $objPHPExcel->getProperties()->setCreator("lj")
                    ->setLastModifiedBy("lj")
                    ->setTitle("Office 2003 XLSX Test Document")
                    ->setSubject("Office 2003 XLSX Test Document")
                    ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
                    ->setKeywords("office 2003 openxml php")
                    ->setCategory("Test result file");
            //设置工作薄索引
            $objPHPExcel->setActiveSheetIndex(0);

            //设置工作薄名称
            $objAtvSheet = $objPHPExcel->getActiveSheet();
            $objAtvSheet->setTitle("生成报表1");

            //设置标题
            $objAtvSheet->setCellValue('A1', '应收客户对账单(箱)');
            $objAtvSheet->mergeCells('A1:Q1'); //合并单元格
            //$objAtvSheet->mergeCells('A2:X2');
            //设置单元格内容
            $objAtvSheet->setCellValue('A2', '序号');
            $objAtvSheet->setCellValue('B2', '日期');
            $objAtvSheet->setCellValue('C2', '客户编号');
            $objAtvSheet->setCellValue('D2', '航母/船次');
            $objAtvSheet->setCellValue('E2', '提单号');
            $objAtvSheet->setCellValue('F2', '箱型');
            $objAtvSheet->setCellValue('G2', '提柜点');
            $objAtvSheet->setCellValue('H2', '装卸点');
            $objAtvSheet->setCellValue('I2', '还柜点');
            $objAtvSheet->setCellValue('J2', '运费');
            $objAtvSheet->setCellValue('K2', '落/还箱费');
            $objAtvSheet->setCellValue('L2', '打/改单费');
            $objAtvSheet->setCellValue('M2', '理货费');
            $objAtvSheet->setCellValue('N2', '港杂费');
            $objAtvSheet->setCellValue('O2', '坏/污箱费');
            $objAtvSheet->setCellValue('P2', '小计');
            $objAtvSheet->setCellValue('Q2', '备注');

            $count = count($object);
            // echo $count;exit;
            $RetentionRow = 3;                //设置开始的一行
            //得到最后一行
            $endLine = $RetentionRow + $count + 1;
            //设置表头背景颜色
            $header_Background = new PHPExcel_Style();
            $header_Background->applyFromArray(
                    array(
                        'fill' => array(
                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                            'color' => array('argb' => 'FF888888')
                        ),
                        'borders' => array(
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($header_Background, "A2:Q2");

            //设置内容的边框,注意：这里要先设置边框 在才能设置单元格里的字体颜色
            $content_Border = new PHPExcel_Style();
            $content_Border->applyFromArray(
                    array(
                        'borders' => array(
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($content_Border, "A3:Q$endLine");
            //开始填充数据到excel
            $i = 0;
            $money_1_sum = 0;
            $money_5_sum = 0;
            $money_6_sum = 0;
            $money_7_sum = 0;
            $money_10_sum = 0;
            $money_11_sum = 0;
            $count_sum = 0;
            foreach ($object as $k => $v) {
                $line = $i + $RetentionRow; //开始填充的行数
                $s1 = $v['id'];
                $s2 = $v['createtime'];
                $s3 = $v['custom_code'];
                $s4 = $v['cmhc'];
                $s5 = $v['lading_code'];
                $s6 = $v['box_id'];
                $s7 = $v['send_yard_id'];
                $s8 = $v['goods_city'];
                $s9 = $v['back_yard_id'];
                $s10 = $v['money_1'];
                $s11 = $v['money_5'];
                $s12 = $v['money_6'];
                $s13 = $v['money_7'];
                $s14 = $v['money_10'];
                $s15 = $v['money_11'];
                $s16 = $v['count'];
                $s17 = $v['money_remark'];
                $money_1_sum += $v['money_1'];
                $money_5_sum += $v['money_5'];
                $money_6_sum += $v['money_6'];
                $money_7_sum += $v['money_7'];
                $money_10_sum += $v['money_10'];
                $money_11_sum += $v['money_11'];
                $count_sum += $v['count'];
                $objAtvSheet->setCellValue("A$line", $s1);
                $objAtvSheet->setCellValue("B$line", $s2);
                $objAtvSheet->setCellValue("C$line", $s3);
                $objAtvSheet->setCellValue("D$line", $s4);
                $objAtvSheet->setCellValue("E$line", $s5);
                $objAtvSheet->setCellValue("F$line", $s6);
                $objAtvSheet->setCellValue("G$line", $s7);
                $objAtvSheet->setCellValue("H$line", $s8);
                $objAtvSheet->setCellValue("I$line", $s9);
                $objAtvSheet->setCellValue("J$line", $s10);
                $objAtvSheet->setCellValue("K$line", $s11);
                $objAtvSheet->setCellValue("L$line", $s12);
                $objAtvSheet->setCellValue("M$line", $s13);
                $objAtvSheet->setCellValue("N$line", $s14);
                $objAtvSheet->setCellValue("O$line", $s15);
                $objAtvSheet->setCellValue("P$line", $s16);
                $objAtvSheet->setCellValue("Q$line", $s17);
                $i++;
            }
            //echo $line;exit;
            $tline = $line + 1;
            $xline = $tline + 1;
            $objAtvSheet->setCellValue("A$tline", "合计:");
            $objAtvSheet->setCellValue("J$tline", "$money_1_sum");
            $objAtvSheet->setCellValue("K$tline", "$money_5_sum");
            $objAtvSheet->setCellValue("L$tline", "$money_6_sum");
            $objAtvSheet->setCellValue("M$tline", "$money_7_sum");
            $objAtvSheet->setCellValue("N$tline", "$money_10_sum");
            $objAtvSheet->setCellValue("O$tline", "$money_11_sum");
            $objAtvSheet->setCellValue("P$tline", "$count_sum");
            $objAtvSheet->setCellValue("A$xline", "合计(大写):");
            $objAtvSheet->setCellValue("J$tline", "$money_1_sum");
            $big_count = get_amount($count_sum);
            $objAtvSheet->setCellValue("J$xline", "$big_count");
            $objAtvSheet->mergeCells("A$tline:I$tline");
            $objAtvSheet->mergeCells("A$xline:I$xline");
            $objAtvSheet->mergeCells("J$xline:Q$xline");
            /*             * **************** */
//                                  echo $mcount; 
//            $objAtvSheet->setCellValue("C$line", "$mcount");
////                                 echo $mktotal1; 
//            $objAtvSheet->setCellValue("D$line", "$mktotal1");
////                                 echo $mktotal2; 
//            $objAtvSheet->setCellValue("E$line", "$mktotal2");
////                                1
//            $objAtvSheet->setCellValue("F$line", "");
////                                 echo $mktotal3; 
//            $objAtvSheet->setCellValue("G$line", "$mktotal3");
////                                1
//            $objAtvSheet->setCellValue("H$line", "");
////                                 echo $mtotal1; 
//            $objAtvSheet->setCellValue("I$line", "$mtotal1");
////                                 echo $mtotal2; 
//            $objAtvSheet->setCellValue("J$line", "$mtotal2");
////                                1
//            $objAtvSheet->setCellValue("K$line", "1");
////                                 echo $mycount; 
//            $objAtvSheet->setCellValue("L$line", "$mycount");
////                                1
//            $objAtvSheet->setCellValue("M$line", "");
////                                 echo $mycount; 
//            $objAtvSheet->setCellValue("N$line", "$mycount");
////                                 echo $mfocunt; 
//            $objAtvSheet->setCellValue("O$line", "$mfocunt");
////                                1
//            $objAtvSheet->setCellValue("P$line", "");
////                                 echo $mfocunt; 
//            $objAtvSheet->setCellValue("Q$line", "$mfocunt");
////                                 echo $mkcount; 
//            $objAtvSheet->setCellValue("R$line", "$mkcount");
////                                 1
//            $objAtvSheet->setCellValue("S$line", "");
////                                 echo $mkcount;  
//            $objAtvSheet->setCellValue("T$line", "$mkcount");
            //设置 字体加粗，居中
            $objAtvSheet->getStyle('A1:Q1')->getFont()->setBold(true);
            $objAtvSheet->getStyle('A1:Q1')->getFont()->setSize(20);
            $objAtvSheet->getStyle('A1:Q1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objAtvSheet->getStyle('A1:Q1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objAtvSheet->getStyle('A1:Q1')->getFont()->setName('新宋体');
            $objAtvSheet->getStyle('A1:Q1')->getFont()->getColor()->setRGB('E85656');
            $objAtvSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置内容 居中
            $objAtvSheet->getStyle("A3:Q$endLine")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格宽度
//    $objAtvSheet->getColumnDimension('A')->setWidth(20);
//    $objAtvSheet->getColumnDimension('B')->setWidth(20);
//    $objAtvSheet->getColumnDimension('C')->setWidth(20);
//    $objAtvSheet->getColumnDimension('D')->setWidth(20);
//    $objAtvSheet->getColumnDimension('E')->setWidth(20);
//    $objAtvSheet->getColumnDimension('F')->setWidth(20);
//    $objAtvSheet->getColumnDimension('G')->setWidth(20);
//    $objAtvSheet->getColumnDimension('H')->setWidth(20);
//    $objAtvSheet->getColumnDimension('I')->setWidth(20);
//    $objAtvSheet->getColumnDimension('J')->setWidth(20);
//    $objAtvSheet->getColumnDimension('K')->setWidth(20);
//    $objAtvSheet->getColumnDimension('L')->setWidth(20);
//    $objAtvSheet->getColumnDimension('M')->setWidth(20);
//    $objAtvSheet->getColumnDimension('N')->setWidth(20);
//    $objAtvSheet->getColumnDimension('O')->setWidth(20);
//    $objAtvSheet->getColumnDimension('P')->setWidth(20);
//    $objAtvSheet->getColumnDimension('Q')->setWidth(20);
//    $objAtvSheet->getColumnDimension('R')->setWidth(20);
//    $objAtvSheet->getColumnDimension('S')->setWidth(20);
//    $objAtvSheet->getColumnDimension('T')->setWidth(20);
//    $objAtvSheet->getColumnDimension('U')->setWidth(20);
//    $objAtvSheet->getColumnDimension('V')->setWidth(20);
//    $objAtvSheet->getColumnDimension('W')->setWidth(20);
//    $objAtvSheet->getColumnDimension('X')->setWidth(20);


            $dateString = date('YmdHis') . "应收客户对账单(箱).xls";
            $outputFileName = $dateString;
            header('Content-Type: application/vnd.ms-excel');
            header('Content-type: text/csv');
            header('Content-Disposition: attachment;filename="' . $outputFileName . '"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
        }
    }

    public function vehiclecostAction() {
        $this->config['layoutEnabled'] = false;
        $servicemode_id = $this->getIntParam('servicemode');
        $status = $this->getIntParam('status');
        $car_id = $this->getIntParam('car_id');
        $driver_id = $this->getIntParam('driver_id');
        $car_team_id = $this->getIntParam('carteam_id');
        $start = $this->getParam('start');
        $end = $this->getParam('end');
        // echo $status;
        /*         * *****主订单过滤******** */
        $zwhere [] = "status = 1";
        if (!empty($servicemode_id)) {
            $zwhere[] = "`service_mode_id` = $servicemode_id";
            $this->view->servicemode_id = $servicemode_id;
        }
        $condition = $this->OrderTable->listAll($zwhere);
        $condition_ids = array();
        foreach ((array) $condition as $k => $v) {
            $condition_ids[] = $v['orderid'];
        }
        $where[] = "`status` = 3";
//        if (!empty($status)) {
//            $where[] = "`status` = $status";
//            $this->view->status = $status;
//        } else {
//            $where[] = "`status` > 0";
//        }
        $condition_ar = implode(",", $condition_ids);
        if (!empty($start)) {
            $where[] = "`dobox_date` >= '{$start}'";
            $this->view->start = $start;
        }
        if (!empty($end)) {
            $where[] = "`dobox_date` <= '{$end}'";
            $this->view->end = $end;
        }
        if (!empty($condition_ar)) {
            $where[] = "`orderid` in ({$condition_ar})";
        }
        if (!empty($car_id)) {
            $where[] = "`car_id` = $car_id";
            $this->view->car_id = $car_id;
        }
        if (!empty($driver_id)) {
            $where[] = "`driver_id` = $driver_id";
            $this->view->driver_id = $driver_id;
        }
        if (!empty($car_team_id)) {
            $where[] = "`car_team_id` = $car_team_id";
            $this->view->carteam_id = $car_team_id;
        }
        $count = $this->OrderdetailTable->listCount($where);
        $pageId = $this->getIntParam('page');
        $pageSize = 5;
        $this->view->paging = $this->getPaging($count, $pageSize, $pageId);
        /** 根据orderid找客户编号* */
        $data = $this->OrderdetailTable->listPage($where, "id desc", $pageSize, $pageId);
        $ids = array();
        $money_ids = array(); //费用信息
        $x1 = array(); //箱型
        $x4 = array(); //车辆信息
        $x5 = array(); //司机信息
        $x6 = array(); //车队信息
        foreach ((array) $data as $k => $v) {
            $ids[] = $v['orderid'];
            $money_ids[] = $v['id'];
            $x1[] = (int) $v['box_id'];
            $x4[] = (int) $v['car_id'];
            $x5[] = (int) $v['driver_id'];
            $x6[] = (int) $v['car_team_id'];
        }
        /*         * *********车辆信息********** */
        $x4 = array_unique($x4);
        $x4_ar = implode(",", $x4);
        if (!empty($x4_ar)) {
            $x4data = $this->CarTable->listAll("`id` in ({$x4_ar}) AND `status` = 1");
        }
        foreach ((array) @$x4data as $k => $v) {
            $x4_data[$v['id']] = $v['name'];
        }
        /*         * *********司机信息**************** */
        $x5 = array_unique($x5);
        $x5_ar = implode(",", $x5);
        if (!empty($x5_ar)) {
            $x5data = $this->DriverTable->listAll("`id` in ({$x5_ar}) AND `status` = 1");
        }
        foreach ((array) @$x5data as $k => $v) {
            $x5_data[$v['id']] = $v['name'];
        }
        /*         * ***********车队信息************** */
        $x6 = array_unique($x6);
        $x6_ar = implode(",", $x6);
        if (!empty($x6_ar)) {
            $x6data = $this->CarteamTable->listAll("`id` in ({$x6_ar}) AND `status` = 1");
        }
        foreach ((array) @$x6data as $k => $v) {
            $x6_data[$v['id']] = $v['nickname'];
        }
        /*         * ******************************** */
        $ids = array_unique($ids);
        $money_ids = array_unique($money_ids);
        $money_ar = implode(",", $money_ids);
        $x1 = array_unique($x1);
        $ids_ar = implode(',', $ids);
        $x1_ar = implode(',', $x1);
        if (!empty($ids_ar)) {
            $orderdata = $this->OrderTable->listAll("`orderid` in ({$ids_ar})");
        }
        if (!empty($money_ar)) {
            $moneydata = $this->OrdermoneyTable->listAll("`detail_id` in ({$money_ar}) AND `type` = 3");
        }
        foreach ((array) @$moneydata as $k => $v) {
            $money_data[$v['detail_id']] = $v;
        }
        if (!empty($x1_ar)) {
            $x1data = $this->BoxTable->listAll("`id` in ({$x1_ar})");
        }
        $order_data = array();
        $x1_data = array();
        $money = array();
        $x2 = array(); //提柜点,还柜点
        $x3 = array(); //门点
        foreach ((array) @$orderdata as $k => $v) {
            $order_data[$v['orderid']] = $v;
            $x2[] = $v['send_yard_id'];
            $x2[] = $v['back_yard_id'];
            $x3[] = $v['custom_site_id'];
        }
        $x2 = array_unique($x2);
        $x2_ar = implode(",", $x2);
        $x3 = array_unique($x3);
        $x3_ar = implode(",", $x3);
        if (!empty($x2_ar)) {
            $x2data = $this->YardTable->listAll("`id` in ({$x2_ar})");
        }
        foreach ((array) @$x2data as $k => $v) {
            $x2_data[$v['id']] = $v['address']; //拿地址
        }
        if (!empty($x3_ar)) {
            $x3data = $this->Custom_siteTable->listAll("id in ({$x3_ar}) AND `status` = 1");
        }
        foreach ((array) @$x1data as $k => $v) {
            $x1_data[$v['id']] = $v['describe']; //箱型拿的是描述
        }
        foreach ((array) @$x3data as $k => $v) {
            $x3_data[$v['id']] = $v['nickname']; //门点简称
        }
        $servicemode = $this->ServicemodeTable->listAll("`status` = 1");
        $carteam = $this->CarteamTable->listAll("`status` = 1");
        $driver = $this->DriverTable->listAll("`status` = 1");
        $car = $this->CarTable->listAll("`status` = 1");
        $this->view->car = isset($car) ? $car : array();
        $this->view->driver = isset($driver) ? $driver : array();
        $this->view->carteam = isset($carteam) ? $carteam : array();
        $this->view->servicemode = isset($servicemode) ? $servicemode : array();
//        $this->view->money = isset($money_data) ? $money_data : array();
//        $this->view->x3 = isset($x3_data) ? $x3_data : array(); //门点简称
//        $this->view->x4 = isset($x4_data) ? $x4_data : array(); //车牌号
//        $this->view->x5 = isset($x5_data) ? $x5_data : array(); //司机姓名
//        $this->view->x6 = isset($x6_data) ? $x6_data : array(); //车队简称
//        $this->view->x1 = isset($x1_data) ? $x1_data : array();
//        $this->view->x2 = isset($x2_data) ? $x2_data : array();
//        $this->view->orderdata = isset($order_data) ? $order_data : array();
//        $this->view->data = isset($data) ? $data : array();
        foreach ($data as $k => $v) {
            $object[$k]['id'] = $v['id'];
            $object[$k]['createtime'] = @$v['createtime'];
            $object[$k]['car_id'] = @$x4_data[$v['car_id']];
            $object[$k]['driver_id'] = @$x5_data[$v['driver_id']];
            $object[$k]['box_id'] = @$x1_data[$v['box_id']];
            $object[$k]['box_code'] = $v['box_code'];
            $object[$k]['lading_code'] = @$order_data[$v['orderid']]['lading_code'];
            $object[$k]['send_yard_id'] = @$x2_data[$order_data[$v['orderid']]['send_yard_id']];
            $object[$k]['goods_city'] = @$x2_data[$order_data[$v['orderid']]['goods_city']];
            $object[$k]['back_yard_id'] = @$x2_data[$order_data[$v['orderid']]['back_yard_id']];
            $object[$k]['money_13'] = @$money_data[$v['id']]['money_13'];
            $object[$k]['xiji'] = @$money_data[$v['id']]['money_1'] + @$money_data[$v['id']]['money_2'] + @$money_data[$v['id']]['money_3'] + @$money_data[$v['id']]['money_4'] + @$money_data[$v['id']]['money_5'] + @$money_data[$v['id']]['money_6'] + @$money_data[$v['id']]['money_7'] + @$money_data[$v['id']]['money_8'] + @$money_data[$v['id']]['money_9'] + @$money_data[$v['id']]['money_10'] + @$money_data[$v['id']]['money_11'] + @$money_data[$v['id']]['money_12'] + @$money_data[$v['id']]['money_13'] + $money_data[$v['id']]['money_14'];
            $object[$k]['money_remark'] = @$money_data[$v['id']]['money_remark'];
        }
        $this->view->object = isset($object) ? $object : array();
        $type = $this->getIntParam("type");
        if ($type == 2) {
            error_reporting(E_ALL);
            require_once('./model/PHPExcel.php');
            date_default_timezone_set('PRC');

            $objPHPExcel = new PHPExcel();

            //设置基本属性
            $objPHPExcel->getProperties()->setCreator("lj")
                    ->setLastModifiedBy("lj")
                    ->setTitle("Office 2003 XLSX Test Document")
                    ->setSubject("Office 2003 XLSX Test Document")
                    ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
                    ->setKeywords("office 2003 openxml php")
                    ->setCategory("Test result file");
            //设置工作薄索引
            $objPHPExcel->setActiveSheetIndex(0);

            //设置工作薄名称
            $objAtvSheet = $objPHPExcel->getActiveSheet();
            $objAtvSheet->setTitle("生成报表1");

            //设置标题
            $objAtvSheet->setCellValue('A1', '车辆成本');
            $objAtvSheet->mergeCells('A1:M1'); //合并单元格
            //$objAtvSheet->mergeCells('A2:X2');
            //设置单元格内容
            $objAtvSheet->setCellValue('A2', '序号');
            $objAtvSheet->setCellValue('B2', '日期');
            $objAtvSheet->setCellValue('C2', '车牌号');
            $objAtvSheet->setCellValue('D2', '司机');
            $objAtvSheet->setCellValue('E2', '箱型');
            $objAtvSheet->setCellValue('F2', '箱号');
            $objAtvSheet->setCellValue('G2', '提单号');
            $objAtvSheet->setCellValue('H2', '提柜点');
            $objAtvSheet->setCellValue('I2', '装卸点');
            $objAtvSheet->setCellValue('J2', '还柜点');
            $objAtvSheet->setCellValue('K2', '扣除代支费');
            $objAtvSheet->setCellValue('L2', '小计');
            $objAtvSheet->setCellValue('M2', '备注');

            $count = count($object);
            // echo $count;exit;
            $RetentionRow = 3;                //设置开始的一行
            //得到最后一行
            $endLine = $RetentionRow + $count + 1;
            //设置表头背景颜色
            $header_Background = new PHPExcel_Style();
            $header_Background->applyFromArray(
                    array(
                        'fill' => array(
                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                            'color' => array('argb' => 'FF888888')
                        ),
                        'borders' => array(
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($header_Background, "A2:M2");

            //设置内容的边框,注意：这里要先设置边框 在才能设置单元格里的字体颜色
            $content_Border = new PHPExcel_Style();
            $content_Border->applyFromArray(
                    array(
                        'borders' => array(
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($content_Border, "A3:M$endLine");
            //开始填充数据到excel
            $i = 0;
            $money_13_sum = 0;
            $xiji_sum = 0;
            foreach ($object as $k => $v) {
                $line = $i + $RetentionRow; //开始填充的行数
                $s1 = $v['id'];
                $s2 = $v['createtime'];
                $s3 = $v['car_id'];
                $s4 = $v['driver_id'];
                $s5 = $v['box_id'];
                ;
                $s6 = $v['box_code'];
                $s7 = $v['lading_code'];
                $s8 = $v['send_yard_id'];
                $s9 = $v['goods_city'];
                $s10 = $v['back_yard_id'];
                $s11 = $v['money_13'];
                $money_13_sum += $s11;
                $s12 = $v['xiji'];
                $xiji_sum += $s12;
                $s13 = $v['money_remark'];
                $objAtvSheet->setCellValue("A$line", $s1);
                $objAtvSheet->setCellValue("B$line", $s2);
                $objAtvSheet->setCellValue("C$line", $s3);
                $objAtvSheet->setCellValue("D$line", $s4);
                $objAtvSheet->setCellValue("E$line", $s5);
                $objAtvSheet->setCellValue("F$line", $s6);
                $objAtvSheet->setCellValue("G$line", $s7);
                $objAtvSheet->setCellValue("H$line", $s8);
                $objAtvSheet->setCellValue("I$line", $s9);
                $objAtvSheet->setCellValue("J$line", $s10);
                $objAtvSheet->setCellValue("K$line", $s11);
                $objAtvSheet->setCellValue("L$line", $s12);
                $objAtvSheet->setCellValue("M$line", $s13);
                $i++;
            }
            //echo $line;exit;
            $tline = $line + 1;
            $xline = $tline + 1;
            $big_count = get_amount($xiji_sum);
             $objAtvSheet->setCellValue("A$tline",'合计:');
             $objAtvSheet->setCellValue("A$xline",'合计(大写):');
             $objAtvSheet->setCellValue("K$tline",$money_13_sum);
             $objAtvSheet->setCellValue("K$xline",$big_count);
             $objAtvSheet->setCellValue("L$tline",$xiji_sum);
            $objAtvSheet->mergeCells("A$tline:J$tline"); //合并单元格
            $objAtvSheet->mergeCells("A$xline:J$xline"); //合并单元格
            $objAtvSheet->mergeCells("K$xline:M$xline"); //合并单元格
            //设置 字体加粗，居中
            $objAtvSheet->getStyle('A1:M1')->getFont()->setBold(true);
            $objAtvSheet->getStyle('A1:M1')->getFont()->setSize(20);
            $objAtvSheet->getStyle('A1:M1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objAtvSheet->getStyle('A1:M1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objAtvSheet->getStyle('A1:M1')->getFont()->setName('新宋体');
            $objAtvSheet->getStyle('A1:M1')->getFont()->getColor()->setRGB('E85656');
            $objAtvSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置内容 居中
            $objAtvSheet->getStyle("A3:M$endLine")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格宽度
//    $objAtvSheet->getColumnDimension('A')->setWidth(20);
//    $objAtvSheet->getColumnDimension('B')->setWidth(20);
//    $objAtvSheet->getColumnDimension('C')->setWidth(20);
//    $objAtvSheet->getColumnDimension('D')->setWidth(20);
//    $objAtvSheet->getColumnDimension('E')->setWidth(20);
//    $objAtvSheet->getColumnDimension('F')->setWidth(20);
//    $objAtvSheet->getColumnDimension('G')->setWidth(20);
//    $objAtvSheet->getColumnDimension('H')->setWidth(20);
//    $objAtvSheet->getColumnDimension('I')->setWidth(20);
//    $objAtvSheet->getColumnDimension('J')->setWidth(20);
//    $objAtvSheet->getColumnDimension('K')->setWidth(20);
//    $objAtvSheet->getColumnDimension('L')->setWidth(20);
//    $objAtvSheet->getColumnDimension('M')->setWidth(20);
//    $objAtvSheet->getColumnDimension('N')->setWidth(20);
//    $objAtvSheet->getColumnDimension('O')->setWidth(20);
//    $objAtvSheet->getColumnDimension('P')->setWidth(20);
//    $objAtvSheet->getColumnDimension('Q')->setWidth(20);
//    $objAtvSheet->getColumnDimension('R')->setWidth(20);
//    $objAtvSheet->getColumnDimension('S')->setWidth(20);
//    $objAtvSheet->getColumnDimension('T')->setWidth(20);
//    $objAtvSheet->getColumnDimension('U')->setWidth(20);
//    $objAtvSheet->getColumnDimension('V')->setWidth(20);
//    $objAtvSheet->getColumnDimension('W')->setWidth(20);
//    $objAtvSheet->getColumnDimension('X')->setWidth(20);


            $dateString = date('YmdHis') . "车辆成本.xls";
            $outputFileName = $dateString;
            header('Content-Type: application/vnd.ms-excel');
            header('Content-type: text/csv');
            header('Content-Disposition: attachment;filename="' . $outputFileName . '"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
        }
// var_dump($object);
    }

    public function ReceivablesAction() {
        $this->config['layoutEnabled'] = false;
        $customid = $this->getIntParam("customid");
        $service_id = $this->getIntParam("service_id");
        $status = $this->getIntParam('status');
        $start = $this->getParam('start');
        $end = $this->getParam('end');
        if ($status) {
            $where[] = "`status` = $status";
            $this->view->status = $status;
        } else {
            $where[] = "`status` > 0";
        }
        if ($customid) {
            $where[] = "`id` = $customid";
            $this->view->customid = $customid;
        }
        $custom = $this->CustomTable->listAll($where);
        foreach ($custom as $k => $v) {
            $order_ids[] = $v['id'];
        }
        $order_ids = @array_unique($order_ids);
        $order_ar = @implode(",", $order_ids);
        $zwhere[] = "`status` = 1";
        if ($service_id) {
            $zwhere[] = "`service_mode_id` = $service_id";
            $this->view->service_id = $service_id;
        }
        if (!empty($order_ar)) {
            $order = $this->OrderTable->listAll("`custom_id` in ({$order_ar})");
        }
        foreach ((array) @$order as $k => $v) {
            $orderid_ids[] = $v['orderid'];
            $custom_orderid[$v['custom_id']][] = $v['orderid']; //custom 对应 order 
        }

        $orderid_ids = @array_unique($orderid_ids);
        $orderid_ar = @implode(",", $orderid_ids);
        $owhere[] = "`orderid` in ({$orderid_ar}) AND `status` = 3";
        if (!empty($start)) {
            $owhere[] = "`dobox_date` >= '{$start}'";
            $this->view->start = $start;
        }
        if (!empty($end)) {
            $owhere[] = "`dobox_date` <= '{$end}'";
            $this->view->end = $end;
        }
        if (!empty($orderid_ar)) {
            $detail = $this->OrderdetailTable->listAll($owhere);
            // $money = $this->OrdermoneyTable->listAll("`orderid` in ({$orderid_ar}) AND `type`= 1 AND `status` = 1");
        }
        foreach ((array) @$detail as $k => $v) {
            $detail_data[$v['orderid']][] = (int) $v['box_id'];
        }
        foreach ((array) @$detail as $k => $v) {
            $date_ids[] = $v['id'];
        }
        $date_ids = @array_unique($date_ids);
        $date_ar = @implode(",", $date_ids);
        //echo $date_ar;
        foreach ((array) @$detail_data as $k => $v) {
            $detail_data[$k] = @array_count_values($v);
        }
        if (!empty($date_ar)) {
            $money = $this->OrdermoneyTable->listAll("`detail_id` in ({$date_ar}) AND `type`= 1 AND `status` = 1");
        }
        foreach ((array) @$money as $k => $v) {
            $money_data[$v['orderid']][] = $v;
        }
        $service = $this->ServicemodeTable->listAll("`status` = 1");
        $boxtype = $this->BoxTable->listAll("`status` = 1");
        foreach ($boxtype as $k => $v) {
            $box_type[$v['id']] = $v;
        }
        $data = array();
                             foreach ((array) $custom as $k => $v) {
                             $data[$k]['id'] = $v['id'];
                             $data[$k]['name'] = $v['name'];
                                        $number = 0;
                                        foreach ((array) @$custom_orderid[$v['id']] as $a1 => $b1) {
                                
                                            $number1 = 0;
                                            foreach ((array) @$detail_data[$b1] as $a2 => $b2) {
                                                $number1 += $b2;
                                            }
                                            $number += $number1;
                                        }
//                                        $countnumber += $number;
                            $data[$k]['number'] = $number;
                                        $money1 = 0;
                                        foreach ((array) @$custom_orderid[$v['id']] as $a3 => $b3) {
                                            $number_1 = 0;
                                            foreach ((array) @$money_data[$b3] as $a4 => $b4) {
                                                // var_dump($b4);
                                                $number_1 += $b4['money_1'];
                                            }
                                            $money1 += $number_1;
                                        }
//                                        $count1 += $money1;
                                     $data[$k]['money1'] = $money1;

                                        $money5 = 0;
                                        foreach ((array) @$custom_orderid[$v['id']] as $a5 => $b5) {
                                            $number_5 = 0;
                                            foreach ((array) @$money_data[$b5] as $a6 => $b6) {
                                                // var_dump($b4);
                                                $number_5 += $b6['money_5'];
                                            }
                                            $money5 += $number_5;
                                        }
//                                        $count2 += $money5;
                                       $data[$k]['money5'] = $money5;

                                        $money6 = 0;
                                        foreach ((array) @$custom_orderid[$v['id']] as $a7 => $b7) {
                                            $number_6 = 0;
                                            foreach ((array) @$money_data[$b7] as $a8 => $b8) {
                                                // var_dump($b4);
                                                $number_6 += $b8['money_6'];
                                            }
                                            $money6 += $number_6;
                                        }
//                                        $count3 += $money6;
                                      $data[$k]['money6'] = $money6;
                                        $money7 = 0;
                                        foreach ((array) @$custom_orderid[$v['id']] as $a9 => $b9) {
                                            $number_7 = 0;
                                            foreach ((array) @$money_data[$b9] as $a10 => $b10) {
                                                // var_dump($b4);
                                                $number_7 += $b10['money_7'];
                                            }
                                            $money7 += $number_7;
                                        }
//                                        $count4 += $money7;
  //                                      echo $money7;
                                      $data[$k]['money7'] = $money7;
                                        $money10 = 0;
                                        foreach ((array) @$custom_orderid[$v['id']] as $a11 => $b11) {
                                            $number_10 = 0;
                                            foreach ((array) @$money_data[$b11] as $a12 => $b12) {
//                                                var_dump($b12);
                                                $number_10 += $b12['money_10'];
                                            }
                                            $money10 += $number_10;
                                        }
//                                        $count5 += $money10;
                                       $data[$k]['money10'] = $money10;
                                        $money11 = 0;

                                        foreach ((array) @$custom_orderid[$v['id']] as $a13 => $b13) {
                                            $number_11 = 0;
                                            foreach ((array) @$money_data[$b13] as $a14 => $b14) {
                                                $number_11 += $b14['money_11'];
                                            }
                                            $money11 += $number_11;
                                        }
//                                        $count6 += $money11;
                                        $data[$k]['money11'] = $money11;
                                        $money15 = 0;

                                        foreach ((array) @$custom_orderid[$v['id']] as $a15 => $b15) {
                                            $number_15 = 0;
                                            foreach ((array) @$money_data[$b15] as $a16 => $b16) {
                                                $number_15 += $b16['money_15'];
                                            }
                                            $money15 += $number_15;
                                        }
//                                        $count7 += $money15;
                                        $data[$k]['money15'] = $money15;
                                        $xj = $money1 + $money5 + $money6 + $money7 + $money10 + $money11 + $money15;
//                                        $xjcount += $xj;
                                        $data[$k]['xj'] = $xj;
                            }
   
      //  var_dump($data);
                                                                $arr = array();
                                    foreach ($detail_data as $k => $v) {
                                        foreach ($v as $k1 => $v1) {
                                            for ($i = 0; $i < $v1; $i++) {
                                                $arr[] = $k1;
                                            }
                                        }
                                    }
                                    $titype = array_count_values($arr);
                                    $text = '';
                                    $b = '';
                                    foreach ($titype as $k => $v) {
                                        $text .= $b . @$box_type[$k]['code'] . "*" . $v;
                                        $b = ',';
                                    }
        $cus = $this->CustomTable->listAll("`status` = 1");
        $this->view->box = isset($box_type) ? $box_type : array();
        $this->view->service = isset($service) ? $service : array();
        $this->view->money = isset($money_data) ? $money_data : array(); //用于运费计算
        $this->view->detail = isset($detail_data) ? $detail_data : array(); //用于箱型计算
        $this->view->custom_id = isset($custom_orderid) ? $custom_orderid : array();
        $this->view->custom = isset($custom) ? $custom : array();
        $this->view->cus = isset($cus) ? $cus : array();
        $type = $this->getIntParam('type');
        if($type == 2){
                       error_reporting(E_ALL);
            require_once('./model/PHPExcel.php');
            date_default_timezone_set('PRC');

            $objPHPExcel = new PHPExcel();

            //设置基本属性
            $objPHPExcel->getProperties()->setCreator("lj")
                    ->setLastModifiedBy("lj")
                    ->setTitle("Office 2003 XLSX Test Document")
                    ->setSubject("Office 2003 XLSX Test Document")
                    ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
                    ->setKeywords("office 2003 openxml php")
                    ->setCategory("Test result file");
            //设置工作薄索引
            $objPHPExcel->setActiveSheetIndex(0);

            //设置工作薄名称
            $objAtvSheet = $objPHPExcel->getActiveSheet();
            $objAtvSheet->setTitle("生成报表1");

            //设置标题
            $objAtvSheet->setCellValue('A1', '应收款汇总');
            $objAtvSheet->mergeCells('A1:M1'); //合并单元格
            //$objAtvSheet->mergeCells('A2:X2');
            //设置单元格内容
            $objAtvSheet->setCellValue('A2', '序号');
            $objAtvSheet->setCellValue('B2', '客户');
            $objAtvSheet->setCellValue('C2', '箱量');
            $objAtvSheet->setCellValue('D2', '运费');
            $objAtvSheet->setCellValue('E2', '落/还箱费');
            $objAtvSheet->setCellValue('F2', '打/改单费');
            $objAtvSheet->setCellValue('G2', '理货费');
            $objAtvSheet->setCellValue('H2', '港杂费');
            $objAtvSheet->setCellValue('I2', '坏/污箱费');
            $objAtvSheet->setCellValue('J2', '代收费');
            $objAtvSheet->setCellValue('K2', '小计');
            $objAtvSheet->setCellValue('L2', '已收');
            $objAtvSheet->setCellValue('M2', '未收');

            $count = count($data);
            // echo $count;exit;
            $RetentionRow = 3;                //设置开始的一行
            //得到最后一行
            $endLine = $RetentionRow + $count + 2;
            //设置表头背景颜色
            $header_Background = new PHPExcel_Style();
            $header_Background->applyFromArray(
                    array(
                        'fill' => array(
                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                            'color' => array('argb' => 'FF888888')
                        ),
                        'borders' => array(
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($header_Background, "A2:M2");

            //设置内容的边框,注意：这里要先设置边框 在才能设置单元格里的字体颜色
            $content_Border = new PHPExcel_Style();
            $content_Border->applyFromArray(
                    array(
                        'borders' => array(
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($content_Border, "A3:M$endLine");
            //开始填充数据到excel
            $i = 0;
            $s3_count = 0;
            $s4_count = 0;
            $s5_count = 0;
            $s6_count = 0;
            $s7_count = 0;
            $s8_count = 0;
            $s9_count = 0;
            $s10_count = 0;
            $s11_count = 0;
            $s12_count = 0;
            $s13_count = 0;
            foreach ($data as $k => $v) {
                $line = $i + $RetentionRow; //开始填充的行数
                $s1 = $v['id'];
                $s2 = $v['name'];
                $s3 = $v['number'];
                $s3_count += $s3;
                $s4 = $v['money1'];
                $s4_count += $s4;
                $s5 = $v['money5'];
                $s5_count += $s5;
                $s6 = $v['money6'];
                $s6_count += $s6;
                $s7 = $v['money7'];
                $s7_count += $s7;
                $s8 = $v['money10'];
                $s8_count += $s8;
                $s9 = $v['money11'];
                $s9_count += $s9;
                $s10 = $v['money15'];
                $s10_count += $s10;
                $s11 = $v['xj'];
                $s11_count += $s11;
                $s12 = '';
                $s12_count += $s12;
                $s13 = $v['xj'];
                $s13_count += $s13;
                $objAtvSheet->setCellValue("A$line", $s1);
                $objAtvSheet->setCellValue("B$line", $s2);
                $objAtvSheet->setCellValue("C$line", $s3);
                $objAtvSheet->setCellValue("D$line", $s4);
                $objAtvSheet->setCellValue("E$line", $s5);
                $objAtvSheet->setCellValue("F$line", $s6);
                $objAtvSheet->setCellValue("G$line", $s7);
                $objAtvSheet->setCellValue("H$line", $s8);
                $objAtvSheet->setCellValue("I$line", $s9);
                $objAtvSheet->setCellValue("J$line", $s10);
                $objAtvSheet->setCellValue("K$line", $s11);
                $objAtvSheet->setCellValue("L$line", $s12);
                $objAtvSheet->setCellValue("M$line", $s13);
                $i++;
            }
            //echo $line;exit;
            $tline = $line + 1;
            $xline = $tline + 1;
            $yline = $xline + 1;
            $big_count = get_amount($s13_count);
             $objAtvSheet->mergeCells("A$tline:B$tline"); //合并单元格
               $objAtvSheet->setCellValue("C$tline",$s3_count);
               $objAtvSheet->setCellValue("D$tline",$s4_count);
               $objAtvSheet->setCellValue("E$tline",$s5_count);
               $objAtvSheet->setCellValue("F$tline",$s6_count);
               $objAtvSheet->setCellValue("G$tline",$s7_count);
               $objAtvSheet->setCellValue("H$tline",$s8_count);
               $objAtvSheet->setCellValue("I$tline",$s9_count);
               $objAtvSheet->setCellValue("J$tline",$s10_count);
               $objAtvSheet->setCellValue("K$tline",$s11_count);
               $objAtvSheet->setCellValue("L$tline",$s12_count);
               $objAtvSheet->setCellValue("M$tline",$s13_count);
               $objAtvSheet->setCellValue("C$xline",$big_count);
               $objAtvSheet->setCellValue("C$yline",$text);
               $objAtvSheet->setCellValue("A$tline","合计:");
               $objAtvSheet->setCellValue("A$xline","合计(大写):");
               $objAtvSheet->setCellValue("A$yline","总箱型箱量");
               
             $objAtvSheet->mergeCells("A$xline:B$xline"); //合并单元格
             $objAtvSheet->mergeCells("A$xline:B$xline"); //合并单元格
             $objAtvSheet->mergeCells("A$yline:B$yline"); //合并单元格
             $objAtvSheet->mergeCells("C$yline:M$yline"); //合并单元格
             $objAtvSheet->mergeCells("C$xline:M$xline"); //合并单元格
//            exit;
            //设置 字体加粗，居中
            $objAtvSheet->getStyle('A1:M1')->getFont()->setBold(true);
            $objAtvSheet->getStyle('A1:M1')->getFont()->setSize(20);
            $objAtvSheet->getStyle('A1:M1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objAtvSheet->getStyle('A1:M1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objAtvSheet->getStyle('A1:M1')->getFont()->setName('新宋体');
            $objAtvSheet->getStyle('A1:M1')->getFont()->getColor()->setRGB('E85656');
            $objAtvSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置内容 居中
            $objAtvSheet->getStyle("A1:M$endLine")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格宽度
//    $objAtvSheet->getColumnDimension('A')->setWidth(20);
//    $objAtvSheet->getColumnDimension('B')->setWidth(20);
//    $objAtvSheet->getColumnDimension('C')->setWidth(20);
//    $objAtvSheet->getColumnDimension('D')->setWidth(20);
//    $objAtvSheet->getColumnDimension('E')->setWidth(20);
//    $objAtvSheet->getColumnDimension('F')->setWidth(20);
//    $objAtvSheet->getColumnDimension('G')->setWidth(20);
//    $objAtvSheet->getColumnDimension('H')->setWidth(20);
//    $objAtvSheet->getColumnDimension('I')->setWidth(20);
//    $objAtvSheet->getColumnDimension('J')->setWidth(20);
//    $objAtvSheet->getColumnDimension('K')->setWidth(20);
//    $objAtvSheet->getColumnDimension('L')->setWidth(20);
//    $objAtvSheet->getColumnDimension('M')->setWidth(20);
//    $objAtvSheet->getColumnDimension('N')->setWidth(20);
//    $objAtvSheet->getColumnDimension('O')->setWidth(20);
//    $objAtvSheet->getColumnDimension('P')->setWidth(20);
//    $objAtvSheet->getColumnDimension('Q')->setWidth(20);
//    $objAtvSheet->getColumnDimension('R')->setWidth(20);
//    $objAtvSheet->getColumnDimension('S')->setWidth(20);
//    $objAtvSheet->getColumnDimension('T')->setWidth(20);
//    $objAtvSheet->getColumnDimension('U')->setWidth(20);
//    $objAtvSheet->getColumnDimension('V')->setWidth(20);
//    $objAtvSheet->getColumnDimension('W')->setWidth(20);
//    $objAtvSheet->getColumnDimension('X')->setWidth(20);


            $dateString = date('YmdHis') . "应付款汇总.xls";
            $outputFileName = $dateString;
            header('Content-Type: application/vnd.ms-excel');
            header('Content-type: text/csv');
            header('Content-Disposition: attachment;filename="' . $outputFileName . '"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
        }
    }

    public function payablesAction() {
        $this->config['layoutEnabled'] = false;
        $team_id = $this->getIntParam('team_id');
        $status = $this->getIntParam('status');
        $service_id = $this->getIntParam('service_id');
        $start = $this->getParam('start');
        $end = $this->getParam('end');
        // echo $status;
        $where = array();
        if ($team_id) {
            $where[] = "`id` = $team_id";
            $this->view->team_id = $team_id;
        }
        if ($status) {
            $where[] = "`status` = $status";
            $this->view->status = $status;
        }
        //$team = $this->CarteamTable->listAll($where);
        $count = $this->CarteamTable->listCount($where);
        $pageId = $this->getIntParam('page');
        $pageSize = 5;
        $this->view->paging = $this->getPaging($count, $pageSize, $pageId);
        $team = $this->CarteamTable->listPage($where, "id desc", $pageSize, $pageId);
        /** 提取车队id */
        $team_ids = array();
        foreach ((array) $team as $k => $v) {
            $team_ids[] = $v['id'];
        }
        $team_ids = array_unique($team_ids);
        $team_ar = implode(",", $team_ids);
        if ($service_id) {
            $owhere[] = "`service_mode_id` = $service_id";
            $this->view->service_id = $service_id;
        }
        if (!empty($start)) {
            $zwhere[] = "`dobox_date` >= '{$start}'";
            $this->view->start = $start;
        }
        if (!empty($end)) {
            $zwhere[] = "`dobox_date` <= '{$end}'";
            $this->view->end = $end;
        }
        if (!empty($team_ar)) {
            $zwhere[] = "`car_team_id` in ({$team_ar}) AND `status` = 3";
            $orderdetail = $this->OrderdetailTable->listAll($zwhere);
        }
        //var_dump($zwhere);
        foreach ((array) @$orderdetail as $k => $v) {
            $orderdetai_data[$v['car_team_id']][$v['orderid']][] = $v['id'];
            $money_ids[] = $v['id'];
            $orderdetai_ids[] = $v['orderid'];
        }
        $money_ids = @array_unique($money_ids);
        $money_ar = @implode(",", $money_ids);
        if (!empty($money_ar)) {
            $money = $this->OrdermoneyTable->listAll("`detail_id` in ({$money_ar}) AND `status` = 1 AND `type` = 3");
        }
        foreach ((array) @$money as $k => $v) {
            $money_data[$v['detail_id']]['m1'] = $v['money_1'] + $v['money_2'] + $v['money_3'] + $v['money_4'] + $v['money_5'] + $v['money_6'] + $v['money_7'] + $v['money_8'] + $v['money_9'] + $v['money_10'] + $v['money_11'] + $v['money_12'] + $v['money_13'] + $v['money_14'];
        }
        //  var_dump($money_data);exit;
        $orderdetai_ids = @array_unique($orderdetai_ids);
        $orderdatai_ar = @implode(",", $orderdetai_ids);
        if (!empty($orderdatai_ar)) {
            $owhere[] = "`orderid` in ({$orderdatai_ar}) AND`status` = 1";
            $order = $this->OrderTable->listAll($owhere); //主订单
        }
        $carteam = $this->CarteamTable->listAll("`status` = 1");
        // var_dump($carteam);
        $data = array();
        foreach ($team as $k => $v) {
            $data[$k]['id'] = $v['id'];
            $data[$k]['name'] = $v['name'];
            $number = 0;
            $count = 0;
            foreach ((array) @$orderdetai_data[$v['id']] as $k1 => $v1) {
                $number += count($v1);
            }
            $data[$k]['number'] = $number;
            foreach ((array) @$orderdetai_data[$v['id']] as $k2 => $v2) {

                foreach ($v2 as $k3 => $v3) {
                    $count += $money_data[$v3]['m1'];
                }
            }
            $data[$k]['count'] = $count;
        }
        $this->view->data = isset($data) ? $data : array();
        $service = $this->ServicemodeTable->listAll("`status` = 1");
        $this->view->carteam = isset($carteam) ? $carteam : array();
        $this->view->service = isset($service) ? $service : array();
        $type = $this->getIntParam('type');
        if($type == 2){
                        error_reporting(E_ALL);
            require_once('./model/PHPExcel.php');
            date_default_timezone_set('PRC');

            $objPHPExcel = new PHPExcel();

            //设置基本属性
            $objPHPExcel->getProperties()->setCreator("lj")
                    ->setLastModifiedBy("lj")
                    ->setTitle("Office 2003 XLSX Test Document")
                    ->setSubject("Office 2003 XLSX Test Document")
                    ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
                    ->setKeywords("office 2003 openxml php")
                    ->setCategory("Test result file");
            //设置工作薄索引
            $objPHPExcel->setActiveSheetIndex(0);

            //设置工作薄名称
            $objAtvSheet = $objPHPExcel->getActiveSheet();
            $objAtvSheet->setTitle("生成报表1");

            //设置标题
            $objAtvSheet->setCellValue('A1', '应付款汇总');
            $objAtvSheet->mergeCells('A1:G1'); //合并单元格
            //$objAtvSheet->mergeCells('A2:X2');
            //设置单元格内容
            $objAtvSheet->setCellValue('A2', '序号');
            $objAtvSheet->setCellValue('B2', '车队');
            $objAtvSheet->setCellValue('C2', '箱量');
            $objAtvSheet->setCellValue('D2', '运费');
            $objAtvSheet->setCellValue('E2', '小计');
            $objAtvSheet->setCellValue('F2', '已付');
            $objAtvSheet->setCellValue('G2', '未付');

            $count = count($data);
            // echo $count;exit;
            $RetentionRow = 3;                //设置开始的一行
            //得到最后一行
            $endLine = $RetentionRow + $count + 1;
            //设置表头背景颜色
            $header_Background = new PHPExcel_Style();
            $header_Background->applyFromArray(
                    array(
                        'fill' => array(
                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                            'color' => array('argb' => 'FF888888')
                        ),
                        'borders' => array(
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($header_Background, "A2:G2");

            //设置内容的边框,注意：这里要先设置边框 在才能设置单元格里的字体颜色
            $content_Border = new PHPExcel_Style();
            $content_Border->applyFromArray(
                    array(
                        'borders' => array(
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($content_Border, "A3:G$endLine");
            //开始填充数据到excel
            $i = 0;
            $numbercount = 0;
            $totalcount = 0;
            foreach ($data as $k => $v) {
                $line = $i + $RetentionRow; //开始填充的行数
                                     $s1 = $v['id'];
                                     $s2 = $v['name']; 
                                     $s3 = $v['number'];
                                     $numbercount += $v['number'];
                                     $s4 = $v['count']; 
                                      $totalcount += $v['count'];
                                     $s5 = $v['count'];
                                     $s6 = '';
                                     $s7 = $v['count'];
                $objAtvSheet->setCellValue("A$line", $s1);
                $objAtvSheet->setCellValue("B$line", $s2);
                $objAtvSheet->setCellValue("C$line", $s3);
                $objAtvSheet->setCellValue("D$line", $s4);
                $objAtvSheet->setCellValue("E$line", $s5);
                $objAtvSheet->setCellValue("F$line", $s6);
                $objAtvSheet->setCellValue("G$line", $s7);
                $i++;
            }
            //echo $line;exit;
            $tline = $line + 1;
            $xline = $tline + 1;
            $big_count = get_amount($totalcount);
            $objAtvSheet->setCellValue("A$tline", '合计:');
            $objAtvSheet->setCellValue("C$tline", $numbercount);
            $objAtvSheet->setCellValue("D$tline",$totalcount);
            $objAtvSheet->setCellValue("E$tline", $totalcount);
            $objAtvSheet->setCellValue("F$tline",'');
            $objAtvSheet->setCellValue("G$tline", $totalcount);
            $objAtvSheet->setCellValue("A$xline", '合计(大写):');
            $objAtvSheet->setCellValue("C$xline", $big_count);
            $objAtvSheet->mergeCells("A$tline:B$tline"); //合并单元格
            $objAtvSheet->mergeCells("A$xline:B$xline"); //合并单元格
            $objAtvSheet->mergeCells("C$xline:G$xline"); //合并单元格
            //设置 字体加粗，居中
            $objAtvSheet->getStyle('A1:G1')->getFont()->setBold(true);
            $objAtvSheet->getStyle('A1:G1')->getFont()->setSize(20);
            $objAtvSheet->getStyle('A1:G1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objAtvSheet->getStyle('A1:G1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objAtvSheet->getStyle('A1:G1')->getFont()->setName('新宋体');
            $objAtvSheet->getStyle('A1:G1')->getFont()->getColor()->setRGB('E85656');
            $objAtvSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置内容 居中
            $objAtvSheet->getStyle("A3:G$endLine")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格宽度
//    $objAtvSheet->getColumnDimension('A')->setWidth(20);
//    $objAtvSheet->getColumnDimension('B')->setWidth(20);
//    $objAtvSheet->getColumnDimension('C')->setWidth(20);
//    $objAtvSheet->getColumnDimension('D')->setWidth(20);
//    $objAtvSheet->getColumnDimension('E')->setWidth(20);
//    $objAtvSheet->getColumnDimension('F')->setWidth(20);
//    $objAtvSheet->getColumnDimension('G')->setWidth(20);
//    $objAtvSheet->getColumnDimension('H')->setWidth(20);
//    $objAtvSheet->getColumnDimension('I')->setWidth(20);
//    $objAtvSheet->getColumnDimension('J')->setWidth(20);
//    $objAtvSheet->getColumnDimension('K')->setWidth(20);
//    $objAtvSheet->getColumnDimension('L')->setWidth(20);
//    $objAtvSheet->getColumnDimension('M')->setWidth(20);
//    $objAtvSheet->getColumnDimension('N')->setWidth(20);
//    $objAtvSheet->getColumnDimension('O')->setWidth(20);
//    $objAtvSheet->getColumnDimension('P')->setWidth(20);
//    $objAtvSheet->getColumnDimension('Q')->setWidth(20);
//    $objAtvSheet->getColumnDimension('R')->setWidth(20);
//    $objAtvSheet->getColumnDimension('S')->setWidth(20);
//    $objAtvSheet->getColumnDimension('T')->setWidth(20);
//    $objAtvSheet->getColumnDimension('U')->setWidth(20);
//    $objAtvSheet->getColumnDimension('V')->setWidth(20);
//    $objAtvSheet->getColumnDimension('W')->setWidth(20);
//    $objAtvSheet->getColumnDimension('X')->setWidth(20);


            $dateString = date('YmdHis') . "应付款汇总.xls";
            $outputFileName = $dateString;
            header('Content-Type: application/vnd.ms-excel');
            header('Content-type: text/csv');
            header('Content-Disposition: attachment;filename="' . $outputFileName . '"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
        }
    }

    public function overviewAction() {
        $this->config['layoutEnabled'] = false;
        /*         * *****以客户为主体******* */
        $custom_id = $this->getIntParam("custom_id");
        $carteam_id = $this->getIntParam("carteam_id");
        $driver_id = $this->getIntParam("driver_id");
        $start = $this->getParam("start");
        $end = $this->getParam("end");
        $car_id = $this->getIntParam("car_id");
        $where[] = "`status` = 1";
        if ($custom_id) {
            $where[] = "`id` = $custom_id";
            $this->view->custom_id = $custom_id;
        }
        if ($carteam_id) {
            $owhere[] = "`car_team_id` = $carteam_id";
            $this->view->carteam_id = $carteam_id;
        }
        if ($driver_id) {
            $owhere[] = "`driver_id` = $driver_id";
            $this->view->driver_id = $driver_id;
        }
        if (!empty($start)) {
            $owhere[] = "`dobox_date` >= '{$start}'";
            $this->view->start = $start;
        }
        if (!empty($end)) {
            $owhere[] = "`dobox_date` <= '{$end}'";
            $this->view->end = $end;
        }
        if (!empty($car_id)) {
            $owhere[] = "`car_id` = $car_id";
            $this->view->car_id = $car_id;
        }
        $custom = $this->CustomTable->listAll($where);
        $data = array(); //数组构建
        if (count($custom)) {
            foreach ($custom as $k => $v) {
                $custom_id_ids[] = $v['id'];
                $data[$v['id']]['name'] = $v['name'];
            }
        }
        $custom_id_ids = array_unique($custom_id_ids);
        $custom_id_ar = implode(",", $custom_id_ids);
        $zwhere[] = "`status` = 1";
        if (!empty($custom_id_ar)) {
            $zwhere[] = "`custom_id` in ({$custom_id_ar})";
            $order = $this->OrderTable->listAll($zwhere);
        }
        foreach ($order as $k => $v) {
            $order_data[$v['custom_id']][] = $v['orderid'];
            $order_detail_ids[] = $v['orderid'];
        }
        $order_detail_ids = array_unique($order_detail_ids);
        //var_dump($order_detail_ids);
        $order_detail_ar = implode(",", $order_detail_ids);
        if (!empty($order_detail_ar)) {
            $owhere[] = "`orderid` in ({$order_detail_ar}) AND `status` = 3";
            $detail = $this->OrderdetailTable->listAll($owhere);
        }
        foreach ($detail as $k => $v) {
            $money_ids[] = $v['id'];
        }
        $money_ids = @array_unique($money_ids);
        $money_ar = @implode(",", $money_ids);
        // echo $money_ar;exit;
        if (!empty($money_ar)) {
            $money = $this->OrdermoneyTable->listAll("`detail_id` in ({$money_ar}) AND `status` = 1 AND `type` = 1");
            $money2 = $this->OrdermoneyTable->listAll("`detail_id` in ({$money_ar}) AND `status` = 1 AND `type` = 2");
            $money3 = $this->OrdermoneyTable->listAll("`detail_id` in ({$money_ar}) AND `status` = 1 AND `type` = 3");
        }
        foreach ((array) @$money as $k => $v) {
            $count_money[$v['orderid']][] = $v;
        }
        foreach ((array) @$money2 as $k => $v) {
            $count_money2[$v['orderid']][] = $v;
        }
        foreach ((array) @$money3 as $k => $v) {
            $count_money3[$v['orderid']][] = $v;
        }
        // var_dump($count_money2);
        foreach ((array) @$count_money as $k => $v) {
            $count1 = 0; //应收代垫费
            $count2 = 0; //应收税金
            $ycount = 0; //应收合计
            foreach ($v as $a => $b) {
                $count1 += $b['money_13'];
                $count2 += $b['money_14'];
                $ycount += ($b['money_1'] + $b['money_2'] + $b['money_3'] + $b['money_4'] + $b['money_5'] + $b['money_6'] + $b['money_7'] + $b['money_8'] + $b['money_9'] + $b['money_10'] + $b['money_11'] + $b['money_12'] + $b['money_13'] + $b['money_14'] + $b['money_15']);
            }
            $money_count[$k]['money_1_13'] = $count1;
            $money_count[$k]['money_1_14'] = $count2;
            $money_count[$k]['ycount'] = $ycount;
        }
        foreach ((array) @$count_money2 as $k => $v) {
            $tcount1 = 0; //扣除代垫费
            $tcount2 = 0; //扣除税金
            $tcount3 = 0; //扣除预付费
            $fcount = 0; //应付合计
            foreach ($v as $a => $b) {
                $tcount1 += $b['money_13'];
                $tcount2 += $b['money_14'];
                $tcount3 += $b['money_15'];
                $fcount += ($b['money_1'] + $b['money_2'] + $b['money_3'] + $b['money_4'] + $b['money_5'] + $b['money_6'] + $b['money_7'] + $b['money_8'] + $b['money_9'] + $b['money_10'] + $b['money_11'] + $b['money_12'] + $b['money_13'] + $b['money_14'] + $b['money_15']);
            }
            //echo $fcount;exit;
            $money_count2[$k]['money_1_13'] = $tcount1;
            $money_count2[$k]['money_1_14'] = $tcount2;
            $money_count2[$k]['money_1_15'] = $tcount3;
            $money_count2[$k]['fcount'] = $fcount;
        }

        foreach ((array) @$count_money3 as $k => $v) {
            $kcount = 0;
            foreach ((array) @$v as $a => $b) {
                $kcount += ($b['money_1'] + $b['money_2'] + $b['money_3'] + $b['money_4'] + $b['money_5'] + $b['money_6'] + $b['money_7'] + $b['money_8'] + $b['money_9'] + $b['money_10'] + $b['money_11'] + $b['money_12'] + $b['money_13'] + $b['money_14']);
            }
            $money_count3[$k]['kcount'] = $kcount;
        }
        foreach ((array) @$detail as $k => $v) {
            $detail_data[$v['orderid']][] = $v['id'];
        }
        // var_dump($data);exit;
        foreach ((array) @$data as $k => $v) {
            $number = 0;
            $total1 = 0;
            $total2 = 0;
            $total3 = 0;
            $ktotal1 = 0;
            $ktotal2 = 0;
            $ktotal3 = 0;
            $ycount = 0;
            $fcount = 0;
            $kcount = 0;
            foreach ((array) @$order_data[$k] as $a => $b) {
                $number += count(@$detail_data[$b]);
                $total1 += @$money_count[$b]['money_1_13'];
                $total2 += @$money_count[$b]['money_1_14'];
                $ycount += @$money_count[$b]['ycount'];
                $ktotal1 += @$money_count2[$b]['money_1_13'];
                $ktotal2 += @$money_count2[$b]['money_1_14'];
                $ktotal3 += @$money_count2[$b]['money_1_15'];
                $fcount += @$money_count2[$b]['fcount'];
                $kcount += @$money_count3[$b]['kcount'];
            }
            $data[$k]['count'] = $number; //箱量
            $data[$k]['total1'] = $total1; //应收代垫费
            $data[$k]['total2'] = $total2; //应收税金
            $data[$k]['ycount'] = $ycount; //应收合计
            $data[$k]['ktotal1'] = $ktotal1; //应收税金
            $data[$k]['ktotal2'] = $ktotal2; //应收税金
            $data[$k]['ktotal3'] = $ktotal3; //应收税金
            $data[$k]['fcount'] = $fcount; //应付合计
            $data[$k]['kcount'] = $kcount; //车辆成本
        }
        //  var_dump($data);
        $car = $this->CarTable->listAll("`status` = 1");
//        var_dump($car);
        $this->view->car = isset($car) ? $car : array();
        $driver = $this->DriverTable->listAll("`status` = 1");
        $custom = $this->CustomTable->listAll("`status` = 1");
        $carteam = $this->CarteamTable->listAll("`status` = 1");
        $this->view->driver = isset($driver) ? $driver : array();
        $this->view->carteam = isset($carteam) ? $carteam : array();
        $this->view->csutom = isset($custom) ? $custom : array();
        $this->view->data = isset($data) ? $data : array();
        $type = $this->getIntParam('type');
        if ($type == 2) {
            error_reporting(E_ALL);
            require_once('./model/PHPExcel.php');
            date_default_timezone_set('PRC');

            $objPHPExcel = new PHPExcel();

            //设置基本属性
            $objPHPExcel->getProperties()->setCreator("lj")
                    ->setLastModifiedBy("lj")
                    ->setTitle("Office 2003 XLSX Test Document")
                    ->setSubject("Office 2003 XLSX Test Document")
                    ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
                    ->setKeywords("office 2003 openxml php")
                    ->setCategory("Test result file");
            //设置工作薄索引
            $objPHPExcel->setActiveSheetIndex(0);

            //设置工作薄名称
            $objAtvSheet = $objPHPExcel->getActiveSheet();
            $objAtvSheet->setTitle("生成报表1");

            //设置标题
            $objAtvSheet->setCellValue('A1', '财务纵览');
            $objAtvSheet->mergeCells('A1:X1'); //合并单元格
            //$objAtvSheet->mergeCells('A2:X2');
            //设置单元格内容
            $objAtvSheet->setCellValue('A2', '序号');
            $objAtvSheet->setCellValue('B2', '客户');
            $objAtvSheet->setCellValue('C2', '箱量');
            $objAtvSheet->setCellValue('D2', '扣除代垫费');
            $objAtvSheet->setCellValue('E2', '扣除税金');
            $objAtvSheet->setCellValue('F2', '扣除代收费');
            $objAtvSheet->setCellValue('G2', '扣除预付费');
            $objAtvSheet->setCellValue('H2', '扣除待支费');
            $objAtvSheet->setCellValue('I2', '扣除代垫费(收)');
            $objAtvSheet->setCellValue('J2', '扣除税金(收)');
            $objAtvSheet->setCellValue('K2', '扣除垫付费');
            $objAtvSheet->setCellValue('L2', '应收合计');
            $objAtvSheet->setCellValue('M2', '已收金额');
            $objAtvSheet->setCellValue('N2', '未收金额');
            $objAtvSheet->setCellValue('O2', '应付合计');
            $objAtvSheet->setCellValue('P2', '已付金额');
            $objAtvSheet->setCellValue('Q2', '未付金额');
            $objAtvSheet->setCellValue('R2', '车辆成本');
            $objAtvSheet->setCellValue('S2', '已付金额');
            $objAtvSheet->setCellValue('T2', '未付金额');
            $objAtvSheet->setCellValue('U2', '公司成本');
            $objAtvSheet->setCellValue('V2', '已付金额');
            $objAtvSheet->setCellValue('W2', '未付金额');
            $objAtvSheet->setCellValue('X2', '公司利润');

            $count = count($data);
            $RetentionRow = 3;                //设置开始的一行
            //得到最后一行
            $endLine = $RetentionRow + $count;

            //设置表头背景颜色
            $header_Background = new PHPExcel_Style();
            $header_Background->applyFromArray(
                    array(
                        'fill' => array(
                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                            'color' => array('argb' => 'FF888888')
                        ),
                        'borders' => array(
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($header_Background, "A2:X2");

            //设置内容的边框,注意：这里要先设置边框 在才能设置单元格里的字体颜色
            $content_Border = new PHPExcel_Style();
            $content_Border->applyFromArray(
                    array(
                        'borders' => array(
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($content_Border, "A3:X$endLine");
            //开始填充数据到excel
            $mcount = 0;
            $mktotal1 = 0;
            $mktotal2 = 0;
            $mktotal3 = 0;
            $mtotal1 = 0;
            $mtotal2 = 0;
            $mycount = 0;
            $mfocunt = 0;
            $mkcount = 0;
            $i = 0;
            foreach ($data as $k => $v) {
                $line = $i + $RetentionRow; //开始填充的行数
                $s1 = $k;
                $s2 = $v['name'];
// echo $v['count'];$mcount += $v['count'];
                $s3 = $v['count'];
                $mktotal1 +=$v['ktotal1'];

                $s4 = $v['ktotal1'];
                $mktotal2 +=$v['ktotal2'];
                $s5 = $v['ktotal2'];
//                                    待支费
                $s6 = '';
                $mktotal3 += $v['ktotal3'];
                $s7 = $v['ktotal3'];
//                                    代收费
                $s8 = '';
                $mtotal1 += $v['total1'];
                $s9 = $v['total1'];
                $mtotal2 += $v['total2'];
                $s10 = $v['total2'];
//                                    垫付费
                $s11 = '';
                $mycount += $v['ycount'];
                $s12 = $v['ycount'];
//                                    已收金额
                $s13 = '';
//                                     echo $v['ycount'] 
                $s14 = $v['ycount'];
                $mfocunt += $v['fcount'];
                $s15 = $v['fcount'];
//已付金额
                $s16 = '';
//                                    echo $v['fcount'] 
                $s17 = $v['fcount'];
                $mkcount += $v['kcount'];
                $s18 = $v['kcount'];
//                                    已付金额
                $s19 = '';
//                                     echo $v['kcount']
                $s20 = $v['kcount'];
                $objAtvSheet->setCellValue("A$line", $s1);
                $objAtvSheet->setCellValue("B$line", $s2);
                $objAtvSheet->setCellValue("C$line", $s3);
                $objAtvSheet->setCellValue("D$line", $s4);
                $objAtvSheet->setCellValue("E$line", $s5);
                $objAtvSheet->setCellValue("F$line", $s6);
                $objAtvSheet->setCellValue("G$line", $s7);
                $objAtvSheet->setCellValue("H$line", $s8);
                $objAtvSheet->setCellValue("I$line", $s9);
                $objAtvSheet->setCellValue("J$line", $s10);
                $objAtvSheet->setCellValue("K$line", $s11);
                $objAtvSheet->setCellValue("L$line", $s12);
                $objAtvSheet->setCellValue("M$line", $s13);
                $objAtvSheet->setCellValue("N$line", $s14);
                $objAtvSheet->setCellValue("O$line", $s15);
                $objAtvSheet->setCellValue("P$line", $s16);
                $objAtvSheet->setCellValue("Q$line", $s17);
                $objAtvSheet->setCellValue("R$line", $s18);
                $objAtvSheet->setCellValue("S$line", $s19);
                $objAtvSheet->setCellValue("T$line", $s20);
                $i++;
            }
            $line = $line + 1;
            // $tline = $line + 1;
            $objAtvSheet->setCellValue("A$line", "合计:");

            $objAtvSheet->mergeCells("A$line:B$line");
            /*             * **************** */
//                                  echo $mcount; 
            $objAtvSheet->setCellValue("C$line", "$mcount");
//                                 echo $mktotal1; 
            $objAtvSheet->setCellValue("D$line", "$mktotal1");
//                                 echo $mktotal2; 
            $objAtvSheet->setCellValue("E$line", "$mktotal2");
//                                1
            $objAtvSheet->setCellValue("F$line", "");
//                                 echo $mktotal3; 
            $objAtvSheet->setCellValue("G$line", "$mktotal3");
//                                1
            $objAtvSheet->setCellValue("H$line", "");
//                                 echo $mtotal1; 
            $objAtvSheet->setCellValue("I$line", "$mtotal1");
//                                 echo $mtotal2; 
            $objAtvSheet->setCellValue("J$line", "$mtotal2");
//                                1
            $objAtvSheet->setCellValue("K$line", "1");
//                                 echo $mycount; 
            $objAtvSheet->setCellValue("L$line", "$mycount");
//                                1
            $objAtvSheet->setCellValue("M$line", "");
//                                 echo $mycount; 
            $objAtvSheet->setCellValue("N$line", "$mycount");
//                                 echo $mfocunt; 
            $objAtvSheet->setCellValue("O$line", "$mfocunt");
//                                1
            $objAtvSheet->setCellValue("P$line", "");
//                                 echo $mfocunt; 
            $objAtvSheet->setCellValue("Q$line", "$mfocunt");
//                                 echo $mkcount; 
            $objAtvSheet->setCellValue("R$line", "$mkcount");
//                                 1
            $objAtvSheet->setCellValue("S$line", "");
//                                 echo $mkcount;  
            $objAtvSheet->setCellValue("T$line", "$mkcount");

            //设置 字体加粗，居中
            $objAtvSheet->getStyle('A1:X1')->getFont()->setBold(true);
            $objAtvSheet->getStyle('A1:X1')->getFont()->setSize(20);
            $objAtvSheet->getStyle('A1:X1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objAtvSheet->getStyle('A1:X1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objAtvSheet->getStyle('A1:X1')->getFont()->setName('新宋体');
            $objAtvSheet->getStyle('A1:X1')->getFont()->getColor()->setRGB('E85656');
            $objAtvSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置内容 居中
            $objAtvSheet->getStyle("A3:X$endLine")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格宽度
//    $objAtvSheet->getColumnDimension('A')->setWidth(20);
//    $objAtvSheet->getColumnDimension('B')->setWidth(20);
//    $objAtvSheet->getColumnDimension('C')->setWidth(20);
//    $objAtvSheet->getColumnDimension('D')->setWidth(20);
//    $objAtvSheet->getColumnDimension('E')->setWidth(20);
//    $objAtvSheet->getColumnDimension('F')->setWidth(20);
//    $objAtvSheet->getColumnDimension('G')->setWidth(20);
//    $objAtvSheet->getColumnDimension('H')->setWidth(20);
//    $objAtvSheet->getColumnDimension('I')->setWidth(20);
//    $objAtvSheet->getColumnDimension('J')->setWidth(20);
//    $objAtvSheet->getColumnDimension('K')->setWidth(20);
//    $objAtvSheet->getColumnDimension('L')->setWidth(20);
//    $objAtvSheet->getColumnDimension('M')->setWidth(20);
//    $objAtvSheet->getColumnDimension('N')->setWidth(20);
//    $objAtvSheet->getColumnDimension('O')->setWidth(20);
//    $objAtvSheet->getColumnDimension('P')->setWidth(20);
//    $objAtvSheet->getColumnDimension('Q')->setWidth(20);
//    $objAtvSheet->getColumnDimension('R')->setWidth(20);
//    $objAtvSheet->getColumnDimension('S')->setWidth(20);
//    $objAtvSheet->getColumnDimension('T')->setWidth(20);
//    $objAtvSheet->getColumnDimension('U')->setWidth(20);
//    $objAtvSheet->getColumnDimension('V')->setWidth(20);
//    $objAtvSheet->getColumnDimension('W')->setWidth(20);
//    $objAtvSheet->getColumnDimension('X')->setWidth(20);


            $dateString = date('YmdHis') . "财务纵览.xls";
            $outputFileName = $dateString;
            header('Content-Type: application/vnd.ms-excel');
            header('Content-type: text/csv');
            header('Content-Disposition: attachment;filename="' . $outputFileName . '"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
        }
    }

    public function expatriateAction() {
        $this->config['layoutEnabled'] = false;
        $servicemode_id = $this->getIntParam('servicemode');
        $car_id = $this->getIntParam('car_id');
        $driver_id = $this->getIntParam('driver_id');
        $car_team_id = $this->getIntParam('carteam_id');
        $start = $this->getParam('start');
        $end = $this->getParam('end');
        // echo $status;
        /*         * *****主订单过滤******** */
        $zwhere[] = "status = 1";
        if (!empty($servicemode_id)) {
            $zwhere[] = "`service_mode_id` = $servicemode_id";
            $this->view->servicemode_id = $servicemode_id;
        }
        $condition = $this->OrderTable->listAll($zwhere);
        $condition_ids = array();
        foreach ((array) $condition as $k => $v) {
            $condition_ids[] = $v['orderid'];
        }
        $where[] = "`status` = 3";
        $condition_ar = implode(",", $condition_ids);
        if (!empty($start)) {
            $where[] = "`dobox_date` >= '{$start}'";
            $this->view->start = $start;
        }
        if (!empty($end)) {
            $where[] = "`dobox_date` <= '{$end}'";
            $this->view->end = $end;
        }
        if (!empty($condition_ar)) {
            $where[] = "`orderid` in ({$condition_ar})";
        }
        if (!empty($car_id)) {
            $where[] = "`car_id` = $car_id";
            $this->view->car_id = $car_id;
        }
        if (!empty($driver_id)) {
            $where[] = "`driver_id` = $driver_id";
            $this->view->driver_id = $driver_id;
        }
        if (!empty($car_team_id)) {
            $where[] = "`car_team_id` = $car_team_id";
            $this->view->carteam_id = $car_team_id;
        }
        $count = $this->OrderdetailTable->listCount($where);
        $pageId = $this->getIntParam('page');
        $pageSize = 5;
        $this->view->paging = $this->getPaging($count, $pageSize, $pageId);
        /** 根据orderid找客户编号* */
        $data = $this->OrderdetailTable->listPage($where, "id desc", $pageSize, $pageId);
        $ids = array();
        $money_ids = array(); //费用信息
        $x1 = array(); //箱型
        $x4 = array(); //车辆信息
        $x5 = array(); //司机信息
        $x6 = array(); //车队信息
        foreach ((array) $data as $k => $v) {
            $ids[] = $v['orderid'];
            $money_ids[] = $v['id'];
            $x1[] = (int) $v['box_id'];
            $x4[] = (int) $v['car_id'];
            $x5[] = (int) $v['driver_id'];
            $x6[] = (int) $v['car_team_id'];
        }
        /*         * *********车辆信息********** */
        $x4 = array_unique($x4);
        $x4_ar = implode(",", $x4);
        if (!empty($x4_ar)) {
            $x4data = $this->CarTable->listAll("`id` in ({$x4_ar}) AND `status` = 1");
        }
        foreach ((array) @$x4data as $k => $v) {
            $x4_data[$v['id']] = $v['name'];
        }
        /*         * *********司机信息**************** */
        $x5 = array_unique($x5);
        $x5_ar = implode(",", $x5);
        if (!empty($x5_ar)) {
            $x5data = $this->DriverTable->listAll("`id` in ({$x5_ar}) AND `status` = 1");
        }
        foreach ((array) @$x5data as $k => $v) {
            $x5_data[$v['id']] = $v['name'];
        }
        /*         * ***********车队信息************** */
        $x6 = array_unique($x6);
        $x6_ar = implode(",", $x6);
        if (!empty($x6_ar)) {
            $x6data = $this->CarteamTable->listAll("`id` in ({$x6_ar}) AND `status` = 1");
        }
        foreach ((array) @$x6data as $k => $v) {
            $x6_data[$v['id']] = $v['nickname'];
        }
        /*         * ******************************** */
        $ids = array_unique($ids);
        $money_ids = array_unique($money_ids);
        $money_ar = implode(",", $money_ids);
        $x1 = array_unique($x1);
        $ids_ar = implode(',', $ids);
        $x1_ar = implode(',', $x1);
        if (!empty($ids_ar)) {
            $orderdata = $this->OrderTable->listAll("`orderid` in ({$ids_ar})");
        }
        if (!empty($money_ar)) {
            $moneydata = $this->OrdermoneyTable->listAll("`detail_id` in ({$money_ar}) AND `type` = 2");
        }
        foreach ((array) @$moneydata as $k => $v) {
            $money_data[$v['detail_id']] = $v;
        }
        if (!empty($x1_ar)) {
            $x1data = $this->BoxTable->listAll("`id` in ({$x1_ar})");
        }
        $order_data = array();
        $x1_data = array();
        $money = array();
        $x2 = array(); //提柜点,还柜点
        $x3 = array(); //门点
        foreach ((array) @$orderdata as $k => $v) {
            $order_data[$v['orderid']] = $v;
            $x2[] = $v['send_yard_id'];
            $x2[] = $v['back_yard_id'];
            $x3[] = $v['custom_site_id'];
        }
        $x2 = array_unique($x2);
        $x2_ar = implode(",", $x2);
        $x3 = array_unique($x3);
        $x3_ar = implode(",", $x3);
        if (!empty($x2_ar)) {
            $x2data = $this->YardTable->listAll("`id` in ({$x2_ar})");
        }
        foreach ((array) @$x2data as $k => $v) {
            $x2_data[$v['id']] = $v['address']; //拿地址
        }
        if (!empty($x3_ar)) {
            $x3data = $this->Custom_siteTable->listAll("id in ({$x3_ar}) AND `status` = 1");
        }
        foreach ((array) @$x1data as $k => $v) {
            $x1_data[$v['id']] = $v['describe']; //箱型拿的是描述
        }
        foreach ((array) @$x3data as $k => $v) {
            $x3_data[$v['id']] = $v['nickname']; //门点简称
        }
        $servicemode = $this->ServicemodeTable->listAll("`status` = 1");
        $carteam = $this->CarteamTable->listAll("`status` = 1");
        $driver = $this->DriverTable->listAll("`status` = 1");
        $car = $this->CarTable->listAll("`status` = 1");
        $this->view->car = isset($car) ? $car : array();
        $this->view->driver = isset($driver) ? $driver : array();
        $this->view->carteam = isset($carteam) ? $carteam : array();
        $this->view->servicemode = isset($servicemode) ? $servicemode : array();

        foreach ($data as $k => $v) {
            $object[$k]['id'] = $v['id'];
            $object[$k]['createtime'] = $v['createtime'];
            $object[$k]['custom_site_id'] = @$x3_data[$order_data[$v['orderid']]['custom_site_id']];
            $object[$k]['lading_code'] = @$order_data[$v['orderid']]['lading_code'];
            $object[$k]['box_code'] = $v['box_code'];
            $object[$k]['box_id'] = @$x1_data[$v['box_id']];
            $object[$k]['car_id'] = @$x4_data[$v['car_id']];
            $object[$k]['send_yard_id'] = @$x2_data[$order_data[$v['orderid']]['send_yard_id']];
            $object[$k]['goods_city'] = @$x2_data[$order_data[$v['orderid']]['goods_city']];
            $object[$k]['back_yard_id'] = @$x2_data[$order_data[$v['orderid']]['back_yard_id']];
            $object[$k]['driver_id'] = @$x5_data[$v['driver_id']];
            $object[$k]['car_team_id'] = @$x6_data[$v['car_team_id']];
            $object[$k]['money_1'] = @$money_data[$v['id']]['money_1'];
//                                  echo $money_1;$money_1_sum += $money_1; 
            $object[$k]['money_13'] = @$money_data[$v['id']]['money_13'];
//                                 echo $money_13;$money_13_sum += $money_13; 
            $object[$k]['money_14'] = @$money_data[$v['id']]['money_14'];
//                                 echo $money_14;$money_14_sum += $money_14;
            $object[$k]['money_15'] = @$money_data[$v['id']]['money_15'];
//                                  echo $money_15;$money_15_sum += $money_15;
            $object[$k]['total'] = @$money_data[$v['id']]['money_1'] + (@$money_data[$v['id']]['money_13'] + @$money_data[$v['id']]['money_14'] + @$money_data[$v['id']]['money_15']);
            //    $total_sum += $total;
        }
        $this->view->object = isset($object) ? $object : array();
        $type = $this->getIntParam("type");
        if ($type == 2) {
            error_reporting(E_ALL);
            require_once('./model/PHPExcel.php');
            date_default_timezone_set('PRC');

            $objPHPExcel = new PHPExcel();

            //设置基本属性
            $objPHPExcel->getProperties()->setCreator("lj")
                    ->setLastModifiedBy("lj")
                    ->setTitle("Office 2003 XLSX Test Document")
                    ->setSubject("Office 2003 XLSX Test Document")
                    ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
                    ->setKeywords("office 2003 openxml php")
                    ->setCategory("Test result file");
            //设置工作薄索引
            $objPHPExcel->setActiveSheetIndex(0);

            //设置工作薄名称
            $objAtvSheet = $objPHPExcel->getActiveSheet();
            $objAtvSheet->setTitle("生成报表1");

            //设置标题
            $objAtvSheet->setCellValue('A1', '应付外派车队对账单');
            $objAtvSheet->mergeCells('A1:Q1'); //合并单元格
            //$objAtvSheet->mergeCells('A2:X2');
            //设置单元格内容
            $objAtvSheet->setCellValue('A2', '序号');
            $objAtvSheet->setCellValue('B2', '日期');
            $objAtvSheet->setCellValue('C2', '门点');
            $objAtvSheet->setCellValue('D2', '提单号');
            $objAtvSheet->setCellValue('E2', '箱号');
            $objAtvSheet->setCellValue('F2', '箱型');
            $objAtvSheet->setCellValue('G2', '车牌号码');
            $objAtvSheet->setCellValue('H2', '提柜点');
            $objAtvSheet->setCellValue('I2', '装卸点');
            $objAtvSheet->setCellValue('J2', '还柜点');
            $objAtvSheet->setCellValue('K2', '司机');
            $objAtvSheet->setCellValue('L2', '车队');
            $objAtvSheet->setCellValue('M2', '运费');
            $objAtvSheet->setCellValue('N2', '扣除代垫费');
            $objAtvSheet->setCellValue('O2', '扣除预付费');
            $objAtvSheet->setCellValue('P2', '扣除税金');
            $objAtvSheet->setCellValue('Q2', '小计');

            $count = count($object);
            $RetentionRow = 3;                //设置开始的一行
            //得到最后一行
            $endLine = $RetentionRow + $count + 1;

            //设置表头背景颜色
            $header_Background = new PHPExcel_Style();
            $header_Background->applyFromArray(
                    array(
                        'fill' => array(
                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                            'color' => array('argb' => 'FF888888')
                        ),
                        'borders' => array(
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($header_Background, "A2:Q2");

            //设置内容的边框,注意：这里要先设置边框 在才能设置单元格里的字体颜色
            $content_Border = new PHPExcel_Style();
            $content_Border->applyFromArray(
                    array(
                        'borders' => array(
                            'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
                        )
                    )
            );
            $objAtvSheet->setSharedStyle($content_Border, "A3:Q$endLine");
            //开始填充数据到excel
            $i = 0;
            $money_1_sum = 0;
            $money_13_sum = 0;
            $money_14_sum = 0;
            $money_15_sum = 0;
            $total_sum = 0;
            foreach ($object as $k => $v) {
                $line = $i + $RetentionRow;
                $s1 = $v['id'];
                $s2 = $v['createtime'];
                $s3 = $v['custom_site_id'];
                $s4 = $v['lading_code'];
                $s5 = $v['box_code'];
                $s6 = $v['box_id'];
                $s7 = $v['car_id'];
                $s8 = $v['send_yard_id'];
                $s9 = $v['goods_city'];
                $s10 = $v['back_yard_id'];
                $s11 = $v['driver_id'];
                $s12 = $v['car_team_id'];
                $s13 = $v['money_1'];
                $money_1_sum += $v['money_1'];
                $s14 = $v['money_13'];
                $money_13_sum += $v['money_13'];
                $s15 = $v['money_14'];
                $money_14_sum += $v['money_14'];
                $s16 = $v['money_15'];
                $money_15_sum += $v['money_15'];
                $s17 = $v['total'];
                $total_sum += $v['total'];
                $objAtvSheet->setCellValue("A$line", $s1);
                $objAtvSheet->setCellValue("B$line", $s2);
                $objAtvSheet->setCellValue("C$line", $s3);
                $objAtvSheet->setCellValue("D$line", $s4);
                $objAtvSheet->setCellValue("E$line", $s5);
                $objAtvSheet->setCellValue("F$line", $s6);
                $objAtvSheet->setCellValue("G$line", $s7);
                $objAtvSheet->setCellValue("H$line", $s8);
                $objAtvSheet->setCellValue("I$line", $s9);
                $objAtvSheet->setCellValue("J$line", $s10);
                $objAtvSheet->setCellValue("K$line", $s11);
                $objAtvSheet->setCellValue("L$line", $s12);
                $objAtvSheet->setCellValue("M$line", $s13);
                $objAtvSheet->setCellValue("N$line", $s14);
                $objAtvSheet->setCellValue("O$line", $s15);
                $objAtvSheet->setCellValue("P$line", $s16);
                $objAtvSheet->setCellValue("Q$line", $s17);
                $i++;
            }
            $line = $line + 1;
            $tline = $line + 1;
            $big_count = get_amount($total_sum);
            $objAtvSheet->setCellValue("A$line", '合计:');
            $objAtvSheet->setCellValue("M$line", $money_1_sum);
            $objAtvSheet->setCellValue("N$line", $money_13_sum);
            $objAtvSheet->setCellValue("O$line", $money_14_sum);
            $objAtvSheet->setCellValue("P$line", $money_15_sum);
            $objAtvSheet->setCellValue("Q$line", $total_sum);
            $objAtvSheet->setCellValue("A$tline", "合计(大写):");
            $objAtvSheet->setCellValue("M$tline", $big_count);
            $objAtvSheet->mergeCells("A$line:L$line"); //合并单元格
            $objAtvSheet->mergeCells("A$tline:L$tline"); //合并单元格
            $objAtvSheet->mergeCells("M$tline:Q$tline"); //合并单元格
            //设置 字体加粗，居中
            $objAtvSheet->getStyle('A1:Q1')->getFont()->setBold(true);
            $objAtvSheet->getStyle('A1:Q1')->getFont()->setSize(20);
            $objAtvSheet->getStyle('A1:Q1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objAtvSheet->getStyle('A1:Q1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objAtvSheet->getStyle('A1:Q1')->getFont()->setName('新宋体');
            $objAtvSheet->getStyle('A1:Q1')->getFont()->getColor()->setRGB('E85656');
            $objAtvSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置内容 居中
            $objAtvSheet->getStyle("A3:Q$endLine")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格宽度
//    $objAtvSheet->getColumnDimension('A')->setWidth(20);
//    $objAtvSheet->getColumnDimension('B')->setWidth(20);
//    $objAtvSheet->getColumnDimension('C')->setWidth(20);
//    $objAtvSheet->getColumnDimension('D')->setWidth(20);
//    $objAtvSheet->getColumnDimension('E')->setWidth(20);
//    $objAtvSheet->getColumnDimension('F')->setWidth(20);
//    $objAtvSheet->getColumnDimension('G')->setWidth(20);
//    $objAtvSheet->getColumnDimension('H')->setWidth(20);
//    $objAtvSheet->getColumnDimension('I')->setWidth(20);
//    $objAtvSheet->getColumnDimension('J')->setWidth(20);
//    $objAtvSheet->getColumnDimension('K')->setWidth(20);
//    $objAtvSheet->getColumnDimension('L')->setWidth(20);
//    $objAtvSheet->getColumnDimension('M')->setWidth(20);
//    $objAtvSheet->getColumnDimension('N')->setWidth(20);
//    $objAtvSheet->getColumnDimension('O')->setWidth(20);
//    $objAtvSheet->getColumnDimension('P')->setWidth(20);
//    $objAtvSheet->getColumnDimension('Q')->setWidth(20);
//    $objAtvSheet->getColumnDimension('R')->setWidth(20);
//    $objAtvSheet->getColumnDimension('S')->setWidth(20);
//    $objAtvSheet->getColumnDimension('T')->setWidth(20);
//    $objAtvSheet->getColumnDimension('U')->setWidth(20);
//    $objAtvSheet->getColumnDimension('V')->setWidth(20);
//    $objAtvSheet->getColumnDimension('W')->setWidth(20);
//    $objAtvSheet->getColumnDimension('X')->setWidth(20);


            $dateString = date('YmdHis') . "应付外派车队对账单.xls";
            $outputFileName = $dateString;
            header('Content-Type: application/vnd.ms-excel');
            header('Content-type: text/csv');
            header('Content-Disposition: attachment;filename="' . $outputFileName . '"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
        }
    }

}

Config::extend('FinanceController', 'Controller');
